CODEWITHSUNDEEP
sqlms-access
TBone2087
TBone2087

Reputation: 71

VBA Access SQL - field within LIKE operator

Can I use a table column within a Like operator? I've created an example,

TableA

Names                    Location 
Albert Smith Senior      Aberdeen  
John Lee                 London  
Michael Rogers Junior    Newcastle  
Mary Roberts             Edinburgh

TableB

Names  
Albert Smith  
John Lee  
Michael Rogers

I want to do a query such as:

SELECT TableA.Location 
into NewTable 
FROM TableA
WHERE TableA.Names Like '*[TableB.Names]*';

In this case, there would be no match for Mary Roberts, Edinburgh but the first three locations would be returned.

Is it possible to put a column into a like statement? If not does anyone have any ideas how I could do this?

Hope you can help

PS I can't use an actual asterisk since this is removed and the text italicised, also I have read about using % instead but this has not worked for me.

Upvotes: 4

Views: 5280

Answers (1)

Christian Specht
Christian Specht

Reputation: 36451

You can join the two tables and use LIKE within the JOIN clause:

SELECT TableA.Location
into NewTable 
FROM TableA
INNER JOIN TableB ON TableA.Names LIKE TableB.Names & '*';

Honestly, I had no idea that you can do this in Access before I tried it just now :-)

Upvotes: 4

Related Questions