Date Criteria Question

[Joe Williams]

I use a lot of queries that resrtict records by looking at the beginning and
ending dates of a form to do a BETWEEN DATE1 and DATE2 statement in the
query.

My question is how do I construct a query such that if the user leaves the
date fields blank it will return all records, but if they fill them in then
it will only give the records for that date range?

As I have it right now (between statement seen above) if the users leaves
the date fields blank then you will get zero records returned.

Thanks

joe

 

[Tom Ellison]

Dear Joe:

I suggest you should move away from using the BETWEEN, and use it's
equivalent. This step would look like:

SomeDate >= DATE1 AND <= DATE2

This won't fix it. It yields the same result. But on this we can
readily build what you want:

(SomeDate >= DATE1 OR DATE1 IS NULL) AND (SomeDate <= DATE2 OR DATE2
IS NULL)

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

 

[Joe Williams]

Tom, works great! thanks for the help

- joe

Dear Joe:

I suggest you should move away from using the BETWEEN, and use it's
equivalent. This step would look like:

SomeDate >= DATE1 AND <= DATE2

This won't fix it. It yields the same result. But on this we can
readily build what you want:

(SomeDate >= DATE1 OR DATE1 IS NULL) AND (SomeDate <= DATE2 OR DATE2
IS NULL)

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts