compare data

[deekras]

i have 2 tables and i want to compare them to see if the same name appears in both tables. of course there can be spelling errors, etc, so i am also comparing on addresses. however even there, there can be errors, like 123 main st. and 123 main street. is there any way to compare them?

i was thinking of comparing on the first 6-8 characters of the address field, so i did a left$ on both addresses. it does get the info as i want it, but then when i put them in a query i get an error message about data type mismatch. i don't understand why. they are both based on text fields.

then i tried to compare on phone numbers. problem is that they are in 2 different formats (one with paretheses, one with dashes). i tried to make them both look the same, using mid$ & right$. and i get the data mismatch error message.

in any case, any ideas on how to compare the data?

 

[araskas]

Even if both fields are text fields if one of them has null in one of the records whole comparision fails. Check for not null on both fields befor you do the comparision.
If you want to make sure that none of these records has nulls sort the table on each field step by step to see how many records have nulls.


[This message has been edited by araskas (edited 03-08-2002).]