-----Original Message-----
Hello Sandra
I believe that the following function will do what is required.
Best regards
Maurice St-Cyr
Micro Systems Consultants, Inc.
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:
'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.
I have a mailing list where 99% of the contacts have our
local area code in the telephone number. I am using the
input mask for telephone numbers but is there a way to
have the area code part a default (but changeable if
required)and the cursor is at the first number for the
phone number - like (956) >xxx-xxxx?
.
