Checkbox Query by Form (1 Viewer)

"I have a query that uses unbound text boxes and combo boxes on a form as criteria. But when I leave some of the boxes blank on the form, no records are returned by the query.

How do I make the query work when some of the boxes are empty?"

Here's a solution to the above question. You can open the main form in the example, enter or select some criteria, and click on the Search button to view the query results on the subform.

Basic Criteria Format
The example basically sets the criteria for each field in a separate column in the query grid like this in query Design view:-
-------------------------------------
Field: [FieldName]=[Forms]![FormName]![ControlName] or [Forms]![FormName]![ControlName] Is Null

Show: uncheck

Criteria: True
-------------------------------------
The [Forms]![FormName]![ControlName] Is Null enables us to leave the text box/combo box on the form blank. The True in the criteria row tells Access to treat the expression in the field row as the criteria.


Besides using the = operator in the expression for exact matches, we can also use other operators. For example, for partial matches of text strings, we can use:-

Field: [FieldName] Like "*" & [Forms]![FormName]![TextBoxName] & "*" or [Forms]![FormName]![TextBoxName] Is Null

In the example, I also included the using of an IIF expression as the criteria for the calculated Age field. When using an IIF expression as criteria, we can use the word True in the IIF to return all the records when the combo box or text box is left blank.

Click to expand...

mike60smart said:

Hi

Are you able to upload a zipped copy of the Db?

Click to expand...