Access: Query to retrieve records that contains keywords stored in a table in Access 2003/XP/2000/97

Question: In Access 2003/XP/2000/97, I'm trying to solve a problem relating to wildcards. I have a table that contains a list of 200 keywords and another table that I want to search using those keywords.

I need to retrieve all records from the second table that contain one of those keywords anywhere in the field, not just "whole field".

How can I do this?

Answer: We'll demonstrate how to do this with the example below.

In this example, we have a table called key_words that contains a list of 200+ keywords in the keys field and a table called Part_names where we want to search for those keywords anywhere in the nomenclature field. This is equivalent to using the LIKE condition as follows:

Like '*keyword*'

To do this, we could create the following query:

The SQL for this query is:

SELECT Part_names.nomenclature, key_words.keys
FROM key_words, Part_names
WHERE (((InStr([Part_names].[nomenclature],[key_words].[keys]))>0));

This query uses the Instr function to check if the nomenclature field contains key anywhere in the value. If it finds key within nomenclature, it will return a value greater than 0.

Running the query above would return the following records: