Retrieving all records when criteria field is null

[Guest]

Try this as the filter

Like
(iif(isnull(Forms![FormName]![FieldName]),"*",Forms![FormName]![FieldName]))

 

[Guest]

Or, another option

Like (NZ(Forms![FormName]![FieldName],"*")

 

[Marshall Barton]

I'm running a query and my field criteria comes from input fields on a form.
If the input field on the form is blank, I want to retrieve all the records
regardless of the value of the specified form. If there is a value in the
field, I want to only retrieve those records that contain that value.

Is there a wildcard value I can use if the value in the form is null so that
I can retrieve all the records? Now when the field is null, no records are
retrieved.

Try changing the criteria to something like:

=Forms!theform!thecontrol OR Forms!theform!thecontrol Is
Null

 

[Jan Il]

Hi Tom & Donna

You computer clock appears to be off by several hours. This can adversely
affect your Certificates. Every Certificate has an expiration and start
date. When your clock time or date is off, even a few minutes or hours, it
may think the Certificates are not valid and may begin to pop up an alert
telling you that it's either not valid yet, or expired, for your own
protection. So, be sure and check that your machine date and time are
accurate.

Jan
MS MVP - IE/OE
Smiles are meant to be shared,
that's why they're so contagious.

Replies are posted only to the newsgroup for the benefit or other readers.
How to make a good newsgroup post:
http://www.dts-l.org/goodpost.htm

 

[Tom & Donna]

I'm running a query and my field criteria comes from input fields on a form.
If the input field on the form is blank, I want to retrieve all the records
regardless of the value of the specified form. If there is a value in the
field, I want to only retrieve those records that contain that value.

Is there a wildcard value I can use if the value in the form is null so that
I can retrieve all the records? Now when the field is null, no records are
retrieved.

Any help would be appreciated.

Thanks

Tom

 

[Tom & Donna]

That worked -- Thanks a lot

Tom

Try changing the criteria to something like:

=Forms!theform!thecontrol OR Forms!theform!thecontrol Is
Null