Query problem with Access Database

[cglover999]

Here is my problem…. I have an Access database that has 2 data fields that I use for a query. The records in the 2 data fields have street address in them. My problem is that sometimes they don’t mach exactly.

Ex: Field 1 might have the following data in it “1234 Woodridge Ter” and Field 2 of the matching record would have “1234 Woodridge Terr”. It’s the same address, but they don’t match exactly.

In my query I want to only see records that don’t have matching street address, but how do I get past this problem? Currently I’m using a simple equation in my query that excludes records that match. Any help would be appreciated.

 

[Andrew Fergus]

Currently I’m using a simple equation in my query that excludes records that match.

What does your query look like at the moment?

Andrew.

 

[Andrew Fergus]

Hi,

I have a query that will match address records up to the 2nd space. It ignores differences such as Ter versus Terr, per your example. I have assumed the first field is called "address" and the 2nd is called "address2". However, I will forewarn you that it doesn't work if there are any "null" records in "address2". I tried to catch the null errors but couldn't on my first attempt, but the following solution should get you started :

New Query
Add the table(s) with the 2 addresses
add address to field 1
add address 2 to field 2
field 3 = IIf( InStr( InStr( [address], " ") +1, [address], " ") = 0, [address], Left( [address], InStr( InStr( [address], " ") + 1, [address], " ") - 1 ))
{note : I have added a number of spaces to prevent any loss through formatting on this forum}
Criteria for field 3 : <>IIf( InStr( InStr( [address2], " ") + 1, [address2], " ") = 0, [address2], Left( [address2], InStr( InStr( [address2], " " ) + 1, [address2], " ") - 1 ))

Save and Run.

This will give you the addresses that don't match up to the 2nd space, showing the first address, the second address and the attempted match.

Please note that this won't work where there is no entry in "address2" and it won't detect any difference between an address such as "10 The Strand" versus "10 The Drive". In this example the 2 addresses would have matched on "10 The".

HTH, Andrew.

P.S. Wherever you see a double quote (" ") in the formula, there is one space between the quotes.