Form Criteria Should Return All Or "Something"

[shuff]

Sometimes it's the pebbles that trip you up...

I have criteria in a query that references two dates (text fields) supplied by a form. The criteria is "Between [DateBegin] and [DateEnd]. Now, if the user supplies values for each of these, everything works great. However, what I want is for the query to return all records (regardless of date) if the user enters NEITHER of the values. But because the query criteria says Between [] and [], it doesn't know what to do if no values are supplied. I want it to behave as if there were no criteria at all for that date field if the two dates are not entered by the user.

What am I missing about how to pull this off?

Thanks,
SLH

 

[Jon K]

You can use an IIf() function in the Where Clause (i.e. the criteria) of the query SQL statement. Switch to the SQL View of the query and edit the Where Clause as follows (using the correct form name and date field name):-

SELECT .....
FROM .....
WHERE IIf(IsNull(Forms![FormName]![DateBegin]) and IsNull(Forms![FormName]![DateEnd]), [DateField]=[DateField], [DateField] between Forms![FormName]![DateBegin] and Forms![FormName]![DateEnd]);


When both text boxes on the form are null (i.e. empty), [DateField]=[DateField] will return every record that has a value in the date field.

(You can even nest more IIf() functions in the expression to tell Access which records to return when only one of the text boxes is empty.)

 

[shuff]

Many thanks - I always know where to go for the right answers!

SLH