Microsoft KB Archive/210579

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

Article ID: 210579

Article Last Modified on 10/11/2006

-

APPLIES TO


 * Microsoft Access 2000 Standard Edition

-



This article was previously published under Q210579



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. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers 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 requirements. NOTE: The sample code in this article uses Microsoft Data Access Objects. For this code to run properly, you must reference the Microsoft DAO 3.6 Object Library. To do so, click References on the Tools menu in the Visual Basic Editor, and make sure that the Microsoft DAO 3.6 Object Library check box is selected.

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

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

Additional query words: telephone

Keywords: kbhowto kbprogramming KB210579

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.