Query By Form - using Text Boxes and Combo Boxes in criteria (1 Viewer)

Status
Not open for further replies.

Jon K

Registered User.
Local time
Today, 23:36
Joined
May 22, 2002
Messages
2,209
"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.


Edited Mar 7, 2006:-
Reloaded an attachment with a modified IIF expression that can accept empty DateOfBirth.
.
 

Attachments

  • Query By Form Access 2000.zip
    30.7 KB · Views: 11,771
Last edited:

Jon K

Registered User.
Local time
Today, 23:36
Joined
May 22, 2002
Messages
2,209
Would like to add these Notes:

(1) We may occasionally see a solution like this:-
-------------------------------------
Field: FieldName

Show: check

Criteria: [Forms]![FormName]![ControlName] or [Forms]![FormName]![ControlName] Is Null
-------------------------------------
It works. However, when the query is saved, Access splits each of such criteria into two columns and two criteria rows, making it very difficult for us to subsequently edit the criteria or add other criteria.


(2) We may also occasionally see a solution like this:-
-------------------------------------
Field: FieldName

Show: check

Criteria: Like "*" & [Forms]![FormName]![ControlName] & "*"
-------------------------------------
Unfortunately, the Like operator cannot return Null values. So when the text box or combo box is left blank, it fails to return all the records if the field happens to contain Null values.
.
 
Status
Not open for further replies.

Users who are viewing this thread

Top Bottom