Multiple Null Reponse Criteria

[Bruister via AccessMonster.com]

I have a query in which I use the criteria 'Like [Job] Or ([Job] Is Null)'
which returns all records if I just hit OK in the parameter box. I wish to
also have a null response criteria in the same query to return records by
date using 'Between [Start:] And [End:] Or (Between [Start:] And [End:] Is
Null)'
Have tried and returns ok for a few attempts then goes crazy. Is it possible
to use 2 null response criteria in same query? Do I write them on the
'criteria line' or the 'or' line? Is there an example of this online
somewhere?

 

[Allen Browne]

Use:
([SomeDateField] >= [Start:] OR [Start:] Is Null) AND
([SomeDateField] < [End:]+1 OR [End:] Is Null)

Notes:
1. Be sure to declare these parameters (Parameters on Query menu), so that
JET knows their data type.

2. The brackets do matter when mixing ANDs and ORs.

3. This will be better than:
Between Nz([Start:], #1/1/1900#) And Nz([End:], #1/1/2999#)
because
a) it's more efficient
b) it's less prone to data typing errors
c) if the date/time field has a time component, the "less than the next day"
returns the records from the final day.

4. It might be better still to generate the filter string dynamically.
There's an examle here:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html