ACC: How to Change Area Codes Based on Telephone Number Prefix

• Microsoft Access versions 1.0, 1.1, 2.0, 7.0, 97

SUMMARY

Advanced: Requires expert coding, interoperability, and multiuser skills.

Some states are adding new telephone area codes based on a phone number's prefix. This article describes how to change a phone number's area code in a table based on the prefix. This is accomplished by creating a table with the prefix you want to find and the new area code. A Visual Basic function then reads this table into an array and searches the phone number table and changes the area code in the phone number table.

IMPORTANT: Before running this function, back up your database and make a copy of your table.

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 versions 1.x and 2.0. For more information about Access Basic, please refer to the "Introduction to Programming" manual in Microsoft Access version 1.x or the "Building Applications" manual in Microsoft Access version 2.0

MORE INFORMATION

To create the function, follow these steps:

1. Create the following table that will contain the phone number prefix to search for and the new area code:

         Table: Area Codes To Change
         ---------------------------
         Field Name: Prefix
            Data type: Text
         Field Name: Area Codes
            Data type: Text
   
   

2. Type the phone number prefixes and area codes in the table Area Codes To Change, for example:

         Prefix      Area Codes
         ----------------------
         635            207
         634            208
   
   

3. Open a module or create a new one, and type the following code:

   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
   

         '=============================================================
         ' Create the following ChgAreaCode() function in the Module
         ' The following function will initialize:
         ' - A variable for the database object.
         ' - Variables for the table objects and several other objects.
         ' - Sets the database object to the opened database.
         ' - Opens table "Area Codes To Change" and tPhName.
         ' - The action of this function is to locate the phone number
         '     entries with certain prefixes and then change the
         '     corresponding area code. This function operates on numbers
         '     with the following format "(206) 635-7050". The table name
         '     and field name containing the phone number must be passed
         '     as arguments.
         '=============================================================
         Function ChgAreaCode (tPhName, fldPhone)
            Dim PhoneDB As Database
            Dim tPhone As Recordset, tPrefix As Recordset
            Dim PCount%
            Dim i%
            Dim tPrefixName$
            Dim Prefix$
            Dim SpacePos%
            Dim HyphenPos%
            Dim PrefixLen%
            Dim PrefixToFind$
            Dim AreaCode$
            Dim Lastfour$
            tPrefixName$ = "Area Codes To Change"
   
            If tPhName = "" Or fldPhone = "" Then Exit Function
   
            Set PhoneDB = CurrentDB()
            Set tPrefix = PhoneDB.OpenRecordset(tPrefixName$)
   
            tPrefix.MoveLast
            PCount% = tPrefix.RecordCount
            tPrefix.MoveFirst
            ReDim PrefixArray$((PCount% - 1), 1)
   
            For i% = 0 To PCount% - 1 Step 1
               PrefixArray$(i%, 0) = tPrefix![Prefix]
               PrefixArray$(i%, 1) = tPrefix![Area Codes]
               tPrefix.MoveNext
            Next i%
            tPrefix.MoveFirst
            tPrefix.Close
   
            Set tPhone = PhoneDB.OpenRecordset(tPhName)
            Do Until tPhone.EOF
               SpacePos% = InStr(1, tPhone(fldPhone), " ")
               HyphenPos% = InStr(SpacePos% + 1, tPhone(fldPhone), "-")
               PrefixLen% = (HyphenPos% - SpacePos%) - 1
               PrefixToFind$ = Mid(tPhone(fldPhone), SpacePos% + _
                                  1, PrefixLen%)
   
               For i% = 0 To PCount% - 1 Step 1
                  If PrefixArray$(i%, 0) = PrefixToFind$ Then
                     AreaCode$ = PrefixArray$(i%, 1)
                     Prefix$ = Mid$(tPhone(fldPhone), 7, 3)
                     Lastfour$ = Right$(tPhone(fldPhone), 4)
                     tPhone.Edit
                     tPhone(fldPhone) = "(" & AreaCode & ") " _
                                           & Prefix & "-" & Lastfour
                     tPhone.Update
                  End If
               Next i%
   
               tPhone.MoveNext
            Loop
            tPhone.Close
            PhoneDB.Close
         End Function
   
   

Example

To call this function from the Debug window (or the Immediate window in versions 1.x and 2.0), type the following line, and then press ENTER

   ?ChgAreaCode(<"Contacts">, "<Business Phone>")

Keywords          : kbprg PgmHowTo PgmObj
Version           : 1.0 1.1 2.0 7.0 97
Platform          : WINDOWS
Hardware          : x86
Issue type        : kbhowto