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

ID: Q210579

The information in this article applies to:

Microsoft Access 2000

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

This article applies only to a Microsoft Access database (.mdb).

SUMMARY

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.

MORE INFORMATION

Microsoft provides programming examples for illustration only, without warranty either expressed or implied, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This article assumes that you are familiar with the programming language being demonstrated and the tools used to create and debug procedures. Microsoft support professionals can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific needs. If you have limited programming experience, you may want to contact a Microsoft Certified Solution Provider or the Microsoft fee-based consulting line at (800) 936-5200. For more information about Microsoft Certified Solution Providers, please see the following page on the World Wide Web:

http://www.microsoft.com/mcsp/

For more information about the support options available from Microsoft, please see the following page on the World Wide Web:

http://www.microsoft.com/support/supportnet/overview/overview.asp

NOTE: The sample code in this article uses Microsoft Data Access Objects. For this code to run properly, you need to reference the Microsoft DAO 3.6 Object Library.

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 Area Codes To Change table. For example:


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

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


'*************************************************************
'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. 
'
'     NOTE: This function operates on numbers stored with the 
'     following format "(425) 635-7050". The table name and 
'     field name containing the phone number must be passed
'     as arguments.
'=============================================================

Function ChgAreaCode (tPhName, fldPhone)
   Dim PhoneDB As DAO.Database
   Dim tPhone As DAO.Recordset, tPrefix As DAO.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 Immediate window, 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.

Additional query words: telephone

Keywords : kbprg AccCon MdlDao
Version : WINDOWS:2000
Platform : WINDOWS
Issue type : kbhowto