Can an Access join act like a wildcard search?

itsquad used Ask the Experts™
In the Query Builder window I have two tables, one has an indexed ItemNo field and in the other table, their is also a ItemNo value in the "Handle Field", however it's in a string that has other values that will of course not exactly match the ItemNo value.  So when I try to join them... no results.

It'd be cool if my join could look through the string for a match and produce the matched results.

I'm sure it's not possible via a join, however what would be a good method to do this?

Thanks, Kevin
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Test your restores, not your backups...
Top Expert 2016
You can actually do this with a JOIN, and a INSTR in the ON clause.  See if this makes sense, and give it a try.

FROM Table1 AS t
INNER JOIN Table2 AS t2 
ON INSTR(t2.Handle, t1.ItemNo) > 0;

Open in new window

Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016
You can also use LIKE,=,<>,INSTR..etc in join conditions..

In this case we can easily use like in join condition.

SELECT columnsYouneed...
FROM yourtable1 AS t1
INNER JOIN yourtable2 AS t2 
ON t2.Handle LIKE t1.ItemNo & "*" 

Open in new window

Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

and if you want all values from Table1 and only the "fuzzy matches" from table 2 use a LEFT JOIN in the above syntax
itsquadSystems Admin


Niiiiice!  Never considered that as an option in the SQL window!  I always just looked at the line in the query builder and double clicked it there to select join type... Wish Access had that pre-programmed in there!  Thanks guys!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial