Query to do a Keyword Search

[businessman]

How do I get a Query to do a keyword search in a Table? So far I get it to do a search but it has to match a specific Field in it's entirety.

 

[RichO]

You can use * as a wildcard character. Search the MS Access help section for an explanation on how to use it.

 

[businessman]

the query wont let me search a table using the "*" symbol. I want to use Like "*" & [Search by word:] & "*" but it has to be able to search all fields for that word.

 

[RichO]

In that case you would probably want to use a small form with a text box for entering the search criteria and a button to run the query.

In the query you would need to select each field that you want to be able to search and as criteria for each field you would use something like:

Like "*" & [Forms]![NameOfForm]![NameOfCriteriaTextBox] & "*"

You will need to continually step down one row (Labeled "Or") using this expression for each field in the query...like this

 

[businessman]

does stepping down like that in the query "OR" all the fields together?

 

[RichO]

Not sure what you mean by "OR" all the fields together....

Basically the query will return records where the keyword entered is found in [Field1] OR [Field2] OR [Field3] OR [Field4].....etc....

 

[businessman]

Is the purpose of the stepping down simply for looks or does that do something

 

[RichO]

It has nothing to do with looks. This explanation is from MS Access help.

You can enter additional criteria for the same field or different fields. When you type expressions in more than one Criteria cell, Microsoft Access combines them using either the And or the Or operator. If the expressions are in different cells in the same row, Microsoft Access uses the And operator, which means only the records that meet the criteria in all the cells will be returned. If the expressions are in different rows of the design grid, Microsoft Access uses the Or operator, which means records that meet criteria in any of the cells will be returned.

 

[businessman]

lol...I figured it wasn't just for looks. Thanks.