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

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 shows you how to change a phone number's area code in a table based on the prefix. You do this by creating a table with the prefix that you want to find and the new area code. A Visual Basic function then reads this table into an array, searches the phone number table, and then changes the area code in the phone number table.

IMPORTANT: Before you run 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:

Create the following table, which 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

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

Prefix Area Codes
635 207
634 208

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

When you use this function, you pass (tPhName), the name of the table that contains the number to change, as a string. Then you pass (fldPhone), the field name that contains the number to change, as a string.

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

where <Contacts> is a table name and "<Business Phone>" is a field containing the phone number.