Comparing telephone numbers

[Bill]

Hi All,

I need to compare loads of telephone number in two separate databases that
have been entered with spaces in different places.

Unless I am missing something I think I need to remove all the spaces first
and then compare the resulting strings. If possible I would like to do this
as a calculated field within a query.

Can anyone give me a couple of pointers please?

Ta.
Bill

 

[Graham R Seach]

Bill,

It may be a tad more complicated that just removing spaces. Some people
include brackets, dashes, and all manner of non-numeric stuff to phone
numbers. What you need to do is remove anything non-numeric and then test
for equivalence.

Use the following code to remove all non-numeric details:
http://www.pacificdb.com.au/MVP/Code/StripChars.htm

From a query, you'd use it like so:
SELECT IIf(StripEx([Phone1], 32) = StripEx([Phone2], 32), True, False)
As NumbersAreTheSame
FROM tblMyTable

32 is decimal for &H20, which is se_AllButNum.

You'll need to add a reference to the Microsoft VBScript Regular Expressions
type library.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

 

[Ken Snell \(MVP\)]

Here is a function that I use to "strip" extraneous characters from phone
number fields:

' ****************************************************
' ** Function StripPhoneNumberPunctuationsAndSpaces **
' ****************************************************

Public Function StripPhoneNumberPunctuationsAndSpaces(ByVal _
varFullPhoneNumber As Variant) As String
' *** THIS FUNCTION REMOVES THE PUNCTUATION AND SPACE CHARACTERS THAT
' *** ARE COMMONLY USED IN PHONE NUMBERS FROM A PHONE NUMBER TEXT STRING.

Dim xstrStrippedPhone As String

On Error Resume Next

xstrStrippedPhone = Nz(varFullPhoneNumber, "")

' remove "." characters
xstrStrippedPhone = Replace(xstrStrippedPhone, ".", "", 1, -1,
vbTextCompare)
' remove " " characters
xstrStrippedPhone = Replace(xstrStrippedPhone, " ", "", 1, -1,
vbTextCompare)
' remove "(" characters
xstrStrippedPhone = Replace(xstrStrippedPhone, "(", "", 1, -1,
vbTextCompare)
' remove ")" characters
xstrStrippedPhone = Replace(xstrStrippedPhone, ")", "", 1, -1,
vbTextCompare)
' remove "-" characters
xstrStrippedPhone = Replace(xstrStrippedPhone, "-", "", 1, -1,
vbTextCompare)

StripPhoneNumberPunctuationsAndSpaces = xstrStrippedPhone
Exit Function
End Function

 

[Bill]

Thanks both for this.
Regards.
Bill.