Using "IN Select" with wildcard? 1

[storm75m]

I have a table with a bunch of addresses.
Table Name: tblLOC
Field Name: ADD1
(Ex: 1234 Maple Way)
I also have a table with a bunch of street names.
Table Name: 221Streets
Field Name: STRName
(Ex: Maple Way)

I want to be able to select all addresses from the first table where the street name is in the second table. I've created this:

SELECT tblLoc.ADD1
FROM tblLoc
WHERE (((tblLoc.ADD1) In (SELECT STRName From 221Streets)))

But it only finds exact matches. Where do I put the wildcard characters, or do I need to separate the street name from the house number first? This is Access2002 BTW, Thanks for your help...

 

[PHV]

You may try this:
SELECT tblLoc.ADD1
FROM tblLoc INNER JOIN 221Streets
ON tblLoc.ADD1 Like '*' & STRName & '*'

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[storm75m]

Get an error saying "Join Expression not supported.

 

[PHV]

Another try:
SELECT tblLoc.ADD1
FROM tblLoc, 221Streets
WHERE tblLoc.ADD1 Like '*' & STRName & '*'

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[storm75m]

Syntax error in From clause..."


Wait, nevermind, I think that worked, thank you very much.

 

[storm75m]

OK, I lied, now I'm not getting any results...

 

[PHV]

Another tries:
WHERE tblLoc.ADD1 Like '*' & Trim(STRName) & '*'

WHERE InStr(1,tblLoc.ADD1,Trim(STRName),1) > 0

...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[storm75m]

Got it, thanks again.

This one worked by the way, my data changed on me and that's why I wasn't getting any results.

SELECT tblLoc.ADD1
FROM tblLoc, 221Streets
WHERE tblLoc.ADD1 Like '*' & STRName & '*'