Truncating Text Fileds

[M B]

Hi all!
I'm trying to find duplicates by matching the address
fields in 2 different tables:

SELECT [Table1].Address, [Table1].City, [Table1].State,
[Tabel1].ZIP
FROM [Table1] INNER JOIN Table ON
([Table1].Address=Table2.Address) AND
([Table1].City=Table2.City);

Even though 123 Main Street in Table1 is the same as 123
Main ST. in Table2, it doesnt query through because the
text is not exactly the same. I tryied using LIKE vs. =
but no luck... Is there a way to have Access match the
number and the first word truncating, trimming or
ignoring whatever is past the second space in the field
(I.e. St., Street, Ave., Lane,etc.)??

Anything would be very much appreciated.
Thanks!!
..

 

[John Spencer (MVP)]

SELECT T1.Address, T1.City, T1.State, T1.ZIP
FROM Table1 as T1 INNER JOIN Table2 as T2
ON T1.City = T2.City AND T1.ZIP = T2.ZIP
WHERE T1.Address LIKE
LEFT(T2.Address,Instr(Instr(1,T2.Address," "),T.2.Address & " "," ")) & "*"

IF I got the expression correct, that should give you all of the T2 Address up
to the second space. If there isn't at least one space in T2.ADDRESS then you
will have an error generated.