Access 2016 Partial Match

Question

I'm trying to query two separate tables for partial matches to the address field.  I have a daily dump of new addresses (located in table new) that I would like to match to existing addresses (table existing).  I'm having no problems matching EXACT matches, but I need to be able to match partial (such as 123 Main St would match 123 Main St as well as 123 Main).  I've tried a Select Query with criteria using Like and Like "*", but I'm still only getting exact matches.  Your help is greatly appreciated!

Answer 1

The LIKE operator works with wildcards (*,?) to produce partial matches. So

WHERE ((([Address]![Address]) Like [New]![sha]))

Will produce only an exact match.

Try it this way:

SELECT New.shn, New.sha, New.rca, New.bill

FROM New, Address

WHERE ((([Address]![Address]) Like "*" & [New]![sha] & "*"))

Answer 2

I truly appreciate your help, but unfortunately switching to the new WHERE statement still did not produce the expected results.  I'm running it through a Query in Access 2016...any other ideas?

Answer 3

It would help if you posted your SQL statement, Are you trying to join the tables or just filter? A WHERE clause like:

Address LIKE "*123 Main*"

should return any records with 123 Main in the address field.

Answer 4

Could you upload the database to cloud storage and a post a link so I could take a look?

Answer 5

I would like to just filter and see exact and partial match.  Here's what I have so far:

SELECT New.shn, New.sha, New.rca, New.bill

FROM New, Address

WHERE ((([Address]![Address]) Like [New]![sha]))

I've also tried this:

WHERE ((([Address]![Address]) In ([New]![sha]))

And I've tried:

WHERE ((([Address]![Address] & "*") Like [New]![sha]))

The Address field has been scrubbed to not include things like St (short for street), La (short for Lane), etc...it only includes things like 123 Main which is why I want the field "sha" to be able to match whether it's 123 Main or 123 Main St.