entering optional query criteria in a form

[Guest]

I have created a form to enter criteria into a query. However, if no value
is entered on the form, I want the query to run using all values in that
field.

I thought I was near success using the IIf function, IIf(expr, truepart,
falsepart), in the criteria field as such :

IIf(IsNull([Forms]![FormA]![BoxA]),"*",[Forms]![FormA]![BoxA])

This function works except that I cannot find the proper way to express that
I want all values used in the truepart. I have tried *, Yes, Is Not Null and
various combinations of quotation marks and parenthesises around these. All
have resulted in my query returning no records if BoxA is null.

What do I need to enter in the truepart in order to encompase all values?
Or is there a better way to achieve my overall goal?

 

[Douglas J. Steele]

There are two common solutions.

The first is to take advantage of the fact that LIKE works the same as =,
and put the following into the Criteria field:

LIKE IIf(IsNull([Forms]![FormA]![BoxA]),"*",[Forms]![FormA]![BoxA])

The other is to put the following:

[Forms]![FormA]![BoxA] OR [Forms]![FormA]![BoxA] IS NULL

 

[John Vinson]

Or is there a better way to achieve my overall goal?

Yes: use a criterion

= [Forms]![FormA]![BoxA] OR [Forms]![FormA]![BoxA] IS NULL

John W. Vinson[MVP]