Combining Nz with a Between two dates query.....

[Guest]

Hi guys

I have a query which uses criteria set on various combo boxes on a form to
determine the results of the query.

I need to add two fields - startdate and enddate for which any results
between those dates and that also meets the other criteria will be shown. For
all the other fields I've used Nz to allow for Null values and now I need to
do the same for the date field. How do I do this?

The obvious:

Between Nz[txtStartDate] AND [txtEndDate]

doesn't work and I'm not sure of the correct syntax or even if this can be
applied in this context. Any help would be most gratefully received.

 

[Allen Browne]

Try:
([txtStartDate] Is Null OR MyDate >= [txtStartDate])
AND ([ttEndDate] Is Null OR MyDate <= [txtEndDate])

The idea is that the condition is satisifed if the text box is blank.
If it is not blank, the condition is satisifed only if the date is in range.

If you are doing this kind of thing on several fields, it is *much* more
efficient to build the Filter, WhereCondition or even the entire SQL
statment dynamically, using only the controls that have a value. If you do
not mind using a bit of code, grab this example:
http://allenbrowne.com/unlinked/Search2000.zip
Just a few kb, and suits Access 2000 or later.