Query Criteria to change with Search Terms

[Adzi]

Hi there,
I have a search form with a text box (unassigned at the moment). The search I want to conduct, filters the product number by the users search string with the use of * as a wildcard option.

I also have a Query with all the product numbers listed, no criteria set. How can I link the the search string in the search form to the query so when the search button is pressed, it will filter the results.

Any help appreciated.

 

[KjWhal]

Hi there,
I have a search form with a text box (unassigned at the moment). The search I want to conduct, filters the product number by the users search string with the use of * as a wildcard option.

I also have a Query with all the product numbers listed, no criteria set. How can I link the the search string in the search form to the query so when the search button is pressed, it will filter the results.

Any help appreciated.

Setting the criteria in the query to Forms![form name]textbox name should do it.

 

[Wiz47]

Setting the criteria in the query to Forms![form name]textbox name should do it.

To take that even a step farther - if you have multiple textboxes, you can query them together or separately, or not at all by using a simple And statement like:

WHERE ((tblMyTable.MyField=Forms!frmMyForm!txtMyField Or Forms!frmMyForm!txtMyField Is Null)=True) AND ((tblMyTable.MyField2=Forms!frmMyForm!txtMyField2 Or Forms!frmMyForm!txtMyField2 Is Null)=True)
AND

so on ...

This will allow you to search for each field separately or together if both (or more) need to be true. If the field is blank (null) then the search ignores it and looks for the next non-null statement. If they are all null, then the seach will return all the fields in the query.

IMHO it is a very straight forward way to accomplish what could be a very complicated search.