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

Last reviewed: August 29, 1997
Article ID: Q96010
The information in this article applies to:
  • 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
    
    
When using this function, you pass (tPhName), the name of the table containing 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.
Keywords          : kbprg PgmHowTo PgmObj
Version           : 1.0 1.1 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.