Microsoft KB Archive/132055

= ACC: How to Convert Letters to Numbers in Phone Numbers =

Article ID: 132055

Article Last Modified on 1/19/2007

-

APPLIES TO


 * Microsoft Access 2.0 Standard Edition
 * Microsoft Access 95 Standard Edition
 * Microsoft Access 97 Standard Edition

-



This article was previously published under Q132055



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



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

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

Option Explicit

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



