Microsoft KB Archive/96010

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

Article ID: 96010

Article Last Modified on 1/18/2007

-

APPLIES TO


 * Microsoft Access 1.0 Standard Edition
 * Microsoft Access 1.1 Standard Edition
 * Microsoft Access 2.0 Standard Edition
 * Microsoft Access 95 Standard Edition
 * Microsoft Access 97 Standard Edition

-



This article was previously published under Q96010



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 &quot;Building Applications with Microsoft Access&quot; 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 &quot;Introduction to Programming&quot; manual in Microsoft Access version 1.x or the &quot;Building Applications&quot; 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:

  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 &quot;Area Codes To Change&quot; 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 &quot;(206) 635-7050&quot;. 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$ = &quot;Area Codes To Change&quot;

If tPhName = &quot;&quot; Or fldPhone = &quot;&quot; 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), &quot; &quot;) HyphenPos% = InStr(SpacePos% + 1, tPhone(fldPhone), &quot;-&quot;) 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) = &quot;(&quot; & AreaCode & &quot;) &quot; _ & Prefix & &quot;-&quot; & 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(<&quot;Contacts&quot;>, &quot;&quot;) where  is a table name and &quot;&quot; is a field containing the phone number.

Keywords: kbhowto kbprogramming KB96010

-

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

© Microsoft Corporation. All rights reserved.