date criteria or all

[DD]

i have a query that i enter begin dates and end dates. i want to be able
also to get all record simly be entering a null in the dates field. is this
possible.

thanks

 

[Ken Snell [MVP]]

Yes.

SELECT *
FROM YourTable
WHERE (DateField Between [Enter start date:] And [Enter end date:])
OR ([Enter start date:] Is Null And [Enter end date:] Is Null);

 

[Douglas J. Steele]

Or, if you'd prefer to, say, only enter a start date but no end date (or
vice versa)

SELECT *
FROM YourTable
WHERE (DateField Between Nz([Enter start date:], #1/1/100#)
And Nz([Enter end date:], #12/31/9999#)

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Yes.

SELECT *
FROM YourTable
WHERE (DateField Between [Enter start date:] And [Enter end date:])
OR ([Enter start date:] Is Null And [Enter end date:] Is Null);
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

i have a query that i enter begin dates and end dates. i want to be able
also to get all record simly be entering a null in the dates field. is
this possible.

thanks

 

[Douglas J. Steele]

Oops. Incorrect parentheses.

SELECT *
FROM YourTable
WHERE DateField BETWEEN Nz([Enter start date:], #1/1/100#)
AND Nz([Enter end date:], #12/31/9999#)

or

SELECT *
FROM YourTable
WHERE (DateField BETWEEN Nz([Enter start date:], #1/1/100#)
AND Nz([Enter end date:], #12/31/9999#))

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Or, if you'd prefer to, say, only enter a start date but no end date (or
vice versa)

SELECT *
FROM YourTable
WHERE (DateField Between Nz([Enter start date:], #1/1/100#)
And Nz([Enter end date:], #12/31/9999#)

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Yes.

SELECT *
FROM YourTable
WHERE (DateField Between [Enter start date:] And [Enter end date:])
OR ([Enter start date:] Is Null And [Enter end date:] Is Null);
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

i have a query that i enter begin dates and end dates. i want to be able
also to get all record simly be entering a null in the dates field. is
this possible.

thanks