ACC1x: Flexible Formatting of Phone Numbers

• Microsoft Access versions 1.0 and 1.1

SUMMARY

This article describes sample functions that you can use to determine the format of a field or control.

MORE INFORMATION

This article assumes that you are familiar with Access Basic and with creating Microsoft Access applications using the programming tools provided with Microsoft Access. For more information on Access Basic, please refer to the "Introduction to Programming" manual.

This example demonstrates a sample function to take the phone number value entered by the user and determine if an area code was entered. If not, it will not display the parentheses.

NOTE: This example does not take into account phone extensions or international numbers.

By creating two functions, intelligent formatting is possible.

NOTE: In the following sample code, an underscore (_) is used as a line-continuation character. Remove the underscore from the end of the line when re-creating this code in Access Basic.

   '**********************************************************
      'Declarations section of the module

   '**********************************************************
         Option Explicit

   '==========================================================
   'The following function is designed for use in the AfterUpdate
   'property of a Form control.
   '   AfterUpdate: =Format_Phone([<control name>])

   '==========================================================
     Function Format_Phone (PhoneNumber As Control)
        '* Exit the function if there is no information passed
        If IsNull(PhoneNumber) Then
           Exit Function
     End If

     '* Need to strip out unwanted characters leaving only numbers
     PhoneNumber = SStrip(PhoneNumber, "-")
     PhoneNumber = SStrip(PhoneNumber, " ")
     PhoneNumber = SStrip(PhoneNumber, ")")
     PhoneNumber = SStrip(PhoneNumber, "(")

     '* Reformat the character string
     Select Case Len(PhoneNumber)
        Case 7
         Screen.ActiveControl = Format(PhoneNumber, "@@@-@@@@")
        Case 10
         Screen.ActiveControl = Format(PhoneNumber, "(@@@) @@@-@@@@")
        Case 11
         'Note that this should be on one line.
        Screen.ActiveControl = Format(PhoneNumber, "@ (@@@) @@@-@@@@")
        Case Else
          MsgBox "This is not a valid phone number."
          Screen.ActiveControl = PhoneNumber
      End Select
     End Function

     Function SStrip (InWord, StripStr)
      Do While InStr(InWord, StripStr)
         'Note that this should be on one line
         InWord = Mid(InWord, 1, InStr(InWord, StripStr) - 1) & _
                  Mid(InWord, InStr(InWord, StripStr) + 1)
      Loop
      SStrip = InWord
     End Function

The SStrip() function is a very important concept. It continues to operate until it eliminates a specified character (StripStr) from the string (InWord). This is used to eliminate extra characters (dashes, spaces, and so on) from the phone number.