ACC: How to Convert Letters to Numbers in Phone Numbers

Last reviewed: August 29, 1997
Article ID: Q132055
The information in this article applies to:
  • Microsoft Access versions 2.0, 7.0, 97

SUMMARY

Moderate: Requires basic macro, coding, and interoperability skills.

This article demonstrates how you can 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 "206-ACC-ESS2" to its equivalent "206- 222-3772" so that dialing is easier.

This article assumes that you are familiar with Visual Basic for Applications and with creating Microsoft Access applications using the programming tools provided with Microsoft Access. For more information about Visual Basic for Applications, please refer to your version of the "Building Applications with Microsoft Access" manual.

NOTE: Visual Basic for Applications is called Access Basic in Microsoft Access version 2.0. For more information about Access Basic, please refer to the "Building Applications" manual.

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 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
    

REFERENCES

For more information about the Mid() function, search the Help Index for "Mid function," or ask the Microsoft Access 97 Office Assistant.

Keywords          : kbprg PgmHowTo MdlGnrl
Version           : 2.0 7.0 97
Platform          : WINDOWS
Hardware          : x86
Issue type        : kbhowto


================================================================================


THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: August 29, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.