Can an Access join act like a wildcard search?

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
Who is Participating?
Bill PrewConnect With a Mentor Commented:
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 KumarConnect With a Mentor Database ExpertCommented:
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 FyeCommented:
and if you want all values from Table1 and only the "fuzzy matches" from table 2 use a LEFT JOIN in the above syntax
itsquadAuthor Commented:
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!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.