Comparing addresses

J

Josh

I would like to compare addresses in two tables to make
sure that they are all the same (the tables are several
thousand rows long), but the problem is many times I'll
have an address in one table as "123 W. Main St." and the
other table it will be "123 West Main Street". Is there
anyway to just compare the numeric value of the address? I
also have to deal with address that might have anywhere
from 1 to 5 (or more, theoretically) numbers as the
numeric part.

Any help would be great, thanks in advance.

Josh
 
J

John Spencer (MVP)

Take a look at the Val function which will return the number part if it is at
the beginning.

If you really need to do this it will take some extensive coding to regularize
the addresses in the two tables and then perform the comparison.

For instance, replace every occurence of
" Street" with " St",
" Avenue" with " Ave",
" Court" with " Court"
etc

Also, you might want to break out the street address into its components in
separate fields. AddressNumber, StreetName, StreetType (Ave, St, Ct, Circle),
AptOrSuite, ...

All in all, not a trivial exercise. But it can be done at various levels of reliability.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top