Inputing Phone Numbers in Database

[fidleid]

I have a database that someone started and it was turned over to me. They put phone numbers in without using an input mask and I need to change about 1000 entries. they entered some phone numbers as (505) 555-1234 and othes 505-555-1234. I need to change all of the 505- to (505) ....I have tried find and replace with no luck can anyone help ... I am just getting started in VB and have a long way to go.

Thanks in Advance.

Dick

 

[Chuck59]

The easiest way to do this is to remove ALL formatting in the text and let Access do it for you:
First, copy and paste the following code into a module and execute it on a COPY of your table.
Then, on the copy, change the format and input mask information to the internal telephone formatting.
Open the table in datasheet view and be sure that your changes were as desired.
If the changes are as desired, rerun the module program with the name of the original table.
Delete the copy.

_ _ _ _ _ _ Program follows _ _ _ _ _
Option Compare Database

Sub FixPhoneNos()
Dim rst As ADODB.Recordset
Dim strPhone As String
Dim strNewPhone As String
Dim intCounter As Integer
Dim LocalAreaCode As String
Dim bSubACs As Boolean
Dim strMyTable As String

'Insert your local area code here:
LocalAreaCode = "800"
'Change False to True to insert default area codes
bSubACs = False
' Substitute YOUR name for the table (Try a copy first!)
strMyTable = "tblPhone"
' Substitute YOUR name for the Phone numbers column
strMyphonelist = "PhoneNos"

Set rst = New ADODB.Recordset
rst.Open strMyTable, CurrentProject.Connection, adOpenKeyset, adLockOptimistic
While Not rst.EOF
strPhone = rst.Fields(strMyphonelist)
iCounter = 1
strNewPhone = ""
If bSubACs Then
If Len(strPhone) <= 8 Then strNewPhone = LocalAreaCode
End If
While iCounter <= Len(strPhone)
If Mid$(strPhone, iCounter, 1) >= &quot;0&quot; And Mid$(strPhone, iCounter, 1) <= &quot;9&quot; Then
strNewPhone = strNewPhone & Mid$(strPhone, iCounter, 1)
End If
iCounter = iCounter + 1
Wend
rst.Fields(strMyphonelist) = strNewPhone
rst.Update
rst.MoveNext
Wend
rst.Close
Set rst = Nothing
End Sub

_ _ _ _ End of routine