ACC2000: How to Convert Letters to Numbers in Phone Numbers

• Microsoft Access 2000

SUMMARY

This article shows you how to create two user-defined functions that you can use to convert any letter in a phone number to its corresponding digit on a telephone keypad or rotary dial. For example, you can convert the number "425-ACC-ESS9" to its equivalent "425-222-3779" so that dialing is easier.

MORE INFORMATION

The method in this article uses two user-defined functions. The first function translates letters into numbers; the second function applies the translation to an entire phone number. The method works with phone numbers that have different formats (for example, "800-555-GIGO" or "800555GIGO") as well as with phone numbers that have different lengths (for example, "555-GIGO" or "011-01-704-555-GIGO").

To convert letters in a phone number to numbers, follow these steps:

1. Create a module and type the following line in the Declarations section if it is not already there:




 Option Explicit 

2. Type the following procedure:




' Translates a letter to a digit.

 Function XlateDigit(ByVal C As String) As String
   C = UCase(C)
   Select Case C
     Case "A" To "P"
       XlateDigit = Chr$((Asc(C) + 1) \ 3 + 28)
     Case "R" To "Y"
       XlateDigit = Chr$(Asc(C) \ 3 + 28)
     Case "Q", "Z"
       XlateDigit = "0"
     Case Else
       XlateDigit = C
   End Select
 End Function 

3. Type the following procedure:




 ' Applies the translated digit to a phone number.

 Function PhoneLettersToDigits(ByVal PhoneNo As Variant) As Variant
   Dim I as Integer
     If VarType(PhoneNo) = 8 Then  ' A string.
       For I = 1 To Len(PhoneNo)
          Mid(PhoneNo, I, 1) = XlateDigit(Mid(PhoneNo, I, 1))
       Next I
     End If
   PhoneLettersToDigits = PhoneNo
 End Function 

4. To test these functions, type the following line in the Immediate window, and then press ENTER:
   

   
    ? PhoneLettersToDigits("PRO-GRA-MMER") 

   Note that the following number is displayed:

   776-472-6637

Additional query words: inf

Keywords : kbprg kbdta AccCon MdlGnrl KbVBA
Version : WINDOWS:2000
Platform : WINDOWS
Issue type : kbhowto