Compare Phone Numbers

[IFORD]

I have a query that has information from two different table on it importcustomer and tblcustomer I would like to compare the phone numbers This is what I have:

SeLECT IMPORTCUSTOMER.[NAME ID], IMPORTCUSTOMER.RESIDENCEPH, tblCustomer.RESPHONE, IMPORTCUSTOMER!RESIDENCEPH Like tblCustomer!RESPHONE AS RPHONE
FROM tblCustomer INNER JOIN IMPORTCUSTOMER ON tblCustomer.CUSTNO = IMPORTCUSTOMER.[NAME ID]
WHERE ((([IMPORTCUSTOMER]![RESIDENCEPH] Like [tblCustomer]![RESPHONE])=0));

I'm looking for the differents between the residenceph and the resphone but it gives me thing that are not differ to me but i can see how it thinks it is differ like

resphone residenceph
(845)555-1234 845-555-1234

How do I write it that it see these as the same.

Thank you Irene

 

[Golom]

You usually want to get rid of spurious characters like ()- and space before you do the comparison

SELECT I.[NAME ID], I.RESIDENCEPH, C.RESPHONE

FROM tblCustomer C INNER JOIN IMPORTCUSTOMER I ON C.CUSTNO = I.[NAME ID]

WHERE 
Replace(Replace(Replace(Replace(I.[RESIDENCEPH],"(",""),")",""),"-","")," ","") 
      <>

Replace(Replace(Replace(Replace(C.[RESPHONE],"(",""),")",""),"-","")," ","")

The LIKE keyword reduces to an equals(=) test if you don't use wildcard characters.

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[IFORD]

Thank you for you help, when I tryed your code I get a data type mismatch in criteria expression and I can not figure out why Do you have any ideas

 

[Golom]

Could be that you have NULLs in some of your fields. Try

SELECT I.[NAME ID], I.RESIDENCEPH, C.RESPHONE

FROM tblCustomer C INNER JOIN IMPORTCUSTOMER I ON C.CUSTNO = I.[NAME ID]

WHERE 
Replace(Replace(Replace(Replace([COLOR=red]NZ([/color]I.[RESIDENCEPH][COLOR=red])[/color],"(",""),")",""),"-","")," ","") 
      <>

Replace(Replace(Replace(Replace([COLOR=red]NZ([/color]C.[RESPHONE][COLOR=red])[/color],"(",""),")",""),"-","")," ","")

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[IFORD]

THANKS THAT WORKS