Search by Text String Question

  • Thread starter Thread starter Qaspec
  • Start date Start date
Q

Qaspec

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] & "*"));
 
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 said:
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 said:
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
Click to expand...
Click to expand...
 
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 said:
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 said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top