remove characters from text fields

[Florida Ad Guy]

I have two imported tables from text files sent to me by clients. Both are
phone number fields in text format. One lists a simple 9 digit sequence for
area code and phone number (eg - 1112223333) and the other imports with phone
formatting (eg - (111) 222-3333).

I know that there are duplicate numbers in the tables (and I need to find
the duplicates). When I run a "Find Unmatched Records" query, the text
formatting prohibits me from finding the truly unique records.

I'm looking for a way to use an update query to change the phone formatted
text to a simple 9 digit string character string -- hence remove the parens,
the space and they hyphen. I'm pretty savy with using wizards and criteria
expressions, but not with actual code. Can anyone tell me how to do this?

 

[Ken Snell \(MVP\)]

Put this function in a regular module:

'********************************
'* *
'* Fxn StripAllNonNumericChars *
'* *
'********************************

' ** This function strips all nonnumeric characters from a text string.

Public Function StripAllNonNumericChars(varOriginalString As Variant) As
String
Dim blnStrip As Boolean
Dim intLoop As Integer
Dim lngLoop As Long
Dim strTemp As String, strChar As String
Dim strOriginalString As String
On Error Resume Next
strTemp = ""
strOriginalString = Nz(varOriginalString, "")
For lngLoop = Len(strOriginalString) To 1 Step -1
blnStrip = True
strChar = Mid(strOriginalString, lngLoop, 1)
For intLoop = Asc("0") To Asc("9")
If strChar = Chr(intLoop) Then
blnStrip = False
Exit For
End If
Next intLoop
If blnStrip = False Then strTemp = strChar & strTemp
Next lngLoop
StripAllNonNumericChars = strTemp
Exit Function
End Function


Then use a query like this to do the update:

UPDATE YourTableName
SET PhoneField = StripAllNonNumericChars(PhoneField);