Multiple Wild Card search

S

Steve Scott

I have eg a list of 10 codes in 1 table. I want a query to take the first
code and then look for occurences of it in another field in another table,
then move onto the second code and then the third. The searches for each
code are to be wildcard searches since the code could be surrounded by other
text. What is the best way of doing this? Does it involve a macro or must
some code be written?
 
C

Carl Rapson

Steve Scott said:
I have eg a list of 10 codes in 1 table. I want a query to take the first
code and then look for occurences of it in another field in another table,
then move onto the second code and then the third. The searches for each
code are to be wildcard searches since the code could be surrounded by
other
text. What is the best way of doing this? Does it involve a macro or must
some code be written?
Click to expand...

I think you'd need VBA code for this. Open a recordset on the list of codes,
then loop through each record in the recordset and build SQL string to do
your other searches.

Carl Rapson
 
M

Marshall Barton

Steve said:
I have eg a list of 10 codes in 1 table. I want a query to take the first
code and then look for occurences of it in another field in another table,
then move onto the second code and then the third. The searches for each
code are to be wildcard searches since the code could be surrounded by other
text. What is the best way of doing this? Does it involve a macro or must
some code be written?
Click to expand...


Try this kind of thing:

SELECT A.anotherfield
FROM [another table] As A INNER JOIN [one table] As O
ON A.anotherfield LIKE "*" & O.code & "*"

Note that you can not do that kind of Join in the QBE so you
must create/edit the query in SQL view.
 
Top