ACC: How to Convert Letters to Numbers in Phone Numbers

MORE INFORMATION

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 Debug window (or Immediate window in Microsoft Access 2.0), and then press ENTER:

   ? PhoneLettersToDigits("PRO-GRA-MMER")

   
   
   Note that the following number is displayed:

   776-472-6637