This function will clean a Phone Number field so that it only has numbers. If your phone number field has a mixture of values such as: (608) 555-1234 or 608-555-1234 or even partial non-numbers characters such as (608-555-1234, etc... you can clean the ( and - values so the field has only numbers using the code below:

(Note: this is ADO code)

Function CleanPhoneNumberField()
Dim PPhone As Variant 'Temp holder for rs!PhoneNmbr value
Dim LPhone As Variant 'length of phone field value
Dim NewPhone As Variant 'New Phone Number after cleaned
Dim CC As Integer 'Count of records fixed
Dim i As Integer 'counter for For-Next Loop
Dim CH As Variant 'Variant to test for numbers
Dim s As Variant 'place holder to copy into NewPhone
Dim strSQL As String
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
strSQL = "Select * from MyTableName"
rs.Open strSQL, CurrentProject.Connection, adOpenDynamic, adLockOptimistic
If rs.EOF And rs.BOF Then
rs.Close
Set rs = Nothing
MsgBox "No records in data file!", vbExclamation
Exit Function
End If
rs.MoveFirst
CC = 0
Do While Not rs.EOF
PPhone = rs!PhoneNmbr
LPhone = Len(PPhone)
s = ""
If LPhone <> 10 Then
For i = 1 To LPhone 'loop through characters in PhoneNmbr field


CH = mid(PPhone, i, 1)
If CH Like "*[0-9]*" Then 'Find just the number values...
s = s & CH 'and save just the number to s variable
End If
Next i
NewPhone = s
CC = CC + 1
rs!PhoneNmbr = NewPhone
rs.Update
End If
NextRS:
rs.MoveNext
Loop
rs.Close
Set rs = Nothing
MsgBox CC & " Phone Numbers have been fixed."
End Function