Query that pulls values from controls that have value selected

[Mordacia]

I have a form that runs queries based on the controls in the form that contain value. I can only select one control at a time and currently run separate queries based on each control: for example, with controls of NAME, DATE, AMOUNT, CATEGORY I can run a query on only one field at a time: either qrysearchname, qrysearchdate, or qrysearchamount, etc...I would like the query to search through all the possible controls/fields and return the values selected so that I could narrow the search by multiple fields instead of just by one. How do I tell the query that unless the control is selected and a criteria is selected, I want all of the records for that field? Example: If I select "Charlie" and "3/15/2002," then I want only Charlie and 3/15/2002, but all amounts and categories?

 

[JerryDennison]

To use a single query with multiple criteria from a form use the following syntax in the criteria property of each field:

Like Forms!FormName!CriteriaFieldName & "*"

If a value is null then it will return all records for that field, so if you left all of the criteria controls empty it would return everything the query is designed to return. Remember, if the criteria share the same row they are "AND'ed" together. If they are on separate rows they or "OR'ed" together.