Comparing Phone #

[pwilliams]

Here is the scenario.... I have a Customer tbl that has about 8000 records. It has a Tele1 field (telephone #) that is unformatted but is a number field i.e. 8063584529. Design changes cannot be made to this table.

I then have Prospective Cust tbl. It has a PhoneNumber field that is a text field, formatted with the appropriate input mask.

I want to compare the two tbls using the last 7 digits of the telephone number. What would be the best way to go about doing this?

 

[PearlGI]

If you create a SELECT query based on each table that contains all fields from the table, but include the following field in place of the tel.no fields.

For the Customer table use Tel: Right(Trim([Tele1]),7)
ie truncates the text field to the last 7 chars

For the ProspectiveCustomer table use Tel: Right(Trim(Str([Tele1])),7)
ie converts number to text and then truncates

You should then be able to compare on the results of these 2 queries, as you're now comparing like with like

 

[Paul_Bricker]

In your query you could set one the TeleNumber to
Right(Tele1,7)

Then put this function in a module

Function CompareNumbers(pholder As String)
Dim valholder As String
pholder = Right([pholder], 8)
Dim x As Integer
For x = 1 To Len(pholder)
If IsNumeric(Mid(pholder, x, 1)) Then
valholder = valholder & Mid(pholder, x, 1)
End If
Next x
CompareNumbers = valholder
End Function

and in your query put

Tele2:CompareNumbers([FormatedPhoneNumberFieldNameHere])

These should return 2 seven digit strings without any input masks to muddy things up.

Paul

 

[pwilliams]

I have tried the first suggestion. For the Prospective Cust tbl I had to trim 8 spaces to the right to get the 7 digits needed, because it is formatted with a phone number input mask , (888-555).

How can I remove the dash from the field, or how do I add a dash to the other field? I can’t compare the two tables with one having the dash and one without – no data is returned.

 

[David R]

If you've saved it with the dash in it

Use Mid and Right to get the sections you want. You'll have to work out for yourself where they start and stop, but the general format is in Access Help.