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

 

[KenSheridan via AccessMonster.com]

You can also make either or both of the parameters optional with:

PARAMETERS [Enter start date:] DATETIME,
[Enter end date:] DATETIME;
SELECT *
FROM YourTable
WHERE (YourDate >= [Enter start date:]
OR [Enter start date:] IS NULL)
AND (YourDate < DATEADD("d",1,[Enter end date:])
OR [Enter end date:] IS NULL);

Note that its a good idea to declare date/time parameters as above to prevent
them being inadvertently misinterpreted as arithmetical expressions if
entered in short date format.

Ken Sheridan
Stafford, England