Search multiple fields with one keyword

[katoc]

Hello.

I need to write a SQL statement that will search an entire table's contents by one keyword.

For example I have TABLE1:

ID
Name
City
Job
Employer
Qualifications

I have a form where the user enters a keyword. I want to check if any of the listed fields above contain that keyword for a report. The long way is to write a SQL query such as: SELECT * from TABLE1 where Name = keyword or City = keyword or Job = keyword or Employer=keyword or Qualifications = keyword;

But is there a better way to do this?

-Sofia

 

[lespaul]

You can do it the way you currently are, you can create a query to combine the information and then search a single field:

SELECT ID, Name As KeyWordField From TableName
UNION
SELECT ID, City From TableName
UNION
SELECT ID, Job From TableName
UNION
SELECT ID, Employer From TableName
UNION
SELEcT ID, Qualifications From TableName

save this query and then search it:

SELECT * FROM newQuery where KeyWordField Like "*Keyword*"

or if you need the original record returned:

SELECT * FROM TableName WHERE ID IN (SELECT ID FROM newQuery where KeyWordField Like "*Keyword*")

Lastly, build a VBA routine that searches each field.

HTH

leslie

 

[PHV]

You may try this:
SELECT * FROM TABLE1
WHERE [keyword] In ([Name],[City],[Job],[Employer],[Qualifications])
Or this:
SELECT * FROM TABLE1
WHERE ';' & [Name] & ';' & [City] & ';' & [Job] & ';' & [Employer] & ';' & [Qualifications] & ';' Like '*;' & [keyword] & ';*'
Or this:
SELECT * FROM TABLE1
WHERE InStr(';' & [Name] & ';' & [City] & ';' & [Job] & ';' & [Employer] & ';' & [Qualifications] & ';', ';' & [keyword] & ';') > 0

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[katoc]

I tried this:

Or this:
SELECT * FROM TABLE1
WHERE InStr(';' & [Name] & ';' & [City] & ';' & [Job] & ';' & [Employer] & ';' & [Qualifications] & ';', ';' & [keyword] & ';') > 0

but I changed the ' to " and it worked. Thanks!