Retrieving all records when criteria field is null

O

Ofer

Try this as the filter

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

Marshall Barton

Tom said:
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.
Click to expand...


Try changing the criteria to something like:

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

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
 
T

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
 
T

Tom & Donna

That worked -- Thanks a lot

Tom
Marshall Barton said:
Try changing the criteria to something like:

=Forms!theform!thecontrol OR Forms!theform!thecontrol Is
Null
Click to expand...
 
Top