Search by Text String Question

If column a contains a word contained in a list from a table I want another
column to list that word.

Example Table Data
Comments
Red Yellow Gray
Blue Green White


Example Table List
Red
Green

Query
Comments KeyWord
Red Yellow Gray Red
Blue Green White Green

Try this --
SELECT [Table Data].Comments, [Table List].KeyWord
FROM [Table Data], [Table List]
WHERE ((([Table Data].Comments) Like "*" & [KeyWord] & "*"));

--
KARL DEWEY
Build a little - Test a little


"Qaspec" wrote:

If column a contains a word contained in a list from a table I want another
column to list that word.

Example Table Data
Comments
Red Yellow Gray
Blue Green White


Example Table List
Red
Green

Query
Comments KeyWord
Red Yellow Gray Red
Blue Green White Green

Thank you that works, but I needed to change the query slightly to show the
records from Recalls where a match could not be found from tblRecallList.
This creates a lot of rows in the KeyWord column that are null. I would like
to replace those null values with the words Tracking Error. I tried an IIf
statement on the KeyWord column but i cant get it to work. Here is my SQL
now.

SELECT Recalls.Comments, tblRecallList.KeyWord
FROM Recalls LEFT JOIN tblRecallList ON Recalls.Type = tblRecallList.Type
WHERE (((Recalls.Comments) Like "*" & [KeyWord] & "*"));

"KARL DEWEY" wrote:

Try this --
SELECT [Table Data].Comments, [Table List].KeyWord
FROM [Table Data], [Table List]
WHERE ((([Table Data].Comments) Like "*" & [KeyWord] & "*"));

--
KARL DEWEY
Build a little - Test a little


"Qaspec" wrote:

If column a contains a word contained in a list from a table I want another
column to list that word.

Example Table Data
Comments
Red Yellow Gray
Blue Green White


Example Table List
Red
Green

Query
Comments KeyWord
Red Yellow Gray Red
Blue Green White Green

Your keyword list can not be in tblRecallList. The yeywords must be in a
table all by themselves - here in [Table List].
SELECT Recalls.Comments
FROM (Recalls LEFT JOIN tblRecallList ON Recalls.Type = tblRecallList.Type)
LEFT JOIN [Table List] ON Recalls.Type = [Table List].KeyWord
WHERE (((Recalls.Comments) Like "*" & [KeyWord] & "*") AND
((tblRecallList.Type) Is Null));

--
KARL DEWEY
Build a little - Test a little


"Qaspec" wrote:

Thank you that works, but I needed to change the query slightly to show the
records from Recalls where a match could not be found from tblRecallList.
This creates a lot of rows in the KeyWord column that are null. I would like
to replace those null values with the words Tracking Error. I tried an IIf
statement on the KeyWord column but i cant get it to work. Here is my SQL
now.

SELECT Recalls.Comments, tblRecallList.KeyWord
FROM Recalls LEFT JOIN tblRecallList ON Recalls.Type = tblRecallList.Type
WHERE (((Recalls.Comments) Like "*" & [KeyWord] & "*"));

"KARL DEWEY" wrote:

Try this --
SELECT [Table Data].Comments, [Table List].KeyWord
FROM [Table Data], [Table List]
WHERE ((([Table Data].Comments) Like "*" & [KeyWord] & "*"));

--
KARL DEWEY
Build a little - Test a little


"Qaspec" wrote:

If column a contains a word contained in a list from a table I want another
column to list that word.

Example Table Data
Comments
Red Yellow Gray
Blue Green White


Example Table List
Red
Green

Query
Comments KeyWord
Red Yellow Gray Red
Blue Green White Green