Microsoft KB Archive/89685

{|
 * width="100%"|

ACC1x: Flexible Formatting of Phone Numbers

 * }

Q89685

-

The information in this article applies to:


 * Microsoft Access versions 1.0, 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 &quot;Introduction to Programming&quot; 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([ ])

'==========================================================    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, &quot;-&quot;) PhoneNumber = SStrip(PhoneNumber, &quot; &quot;) PhoneNumber = SStrip(PhoneNumber, &quot;)&quot;)    PhoneNumber = SStrip(PhoneNumber, &quot;(&quot;)

'* Reformat the character string Select Case Len(PhoneNumber) Case 7 Screen.ActiveControl = Format(PhoneNumber, &quot;@@@-@@@@&quot;) Case 10 Screen.ActiveControl = Format(PhoneNumber, &quot;(@@@) @@@-@@@@&quot;) Case 11 'Note that this should be on one line. Screen.ActiveControl = Format(PhoneNumber, &quot;@ (@@@) @@@-@@@@&quot;) Case Else MsgBox &quot;This is not a valid phone number.&quot; 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 Format_Phone function massages the phone number into a common string (void of parentheses, spaces, and dashes) and then determines the most appropriate format. This function returns the phone number back to the form in that format.

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.

Additional query words: mask input format telephone

Keywords : kbprg

Issue type : kbinfo

Technology :