Help with a date range query problem

[hardrock]

Hi all, Can someone please help me with a date range query. Basically I have a query that shows delivery dates, from 01/01/2006 to 01/01/2009.
I just want to modify the query so it shows all deliveries within the past 21 days and all outstanding deliveries until 2009. Please note that in some cases, the delivery date may have been left blank in the table (as this it maybe unknown). If that is the case, the query criterion needs to pick that up as an outstanding delivery. Can someone help me with this? So far I’ve figured out

Between Date() And Date()-"21"

Thanks

 

[statsman]

You could try:

Between [Start Date] And [End Date]

The query will prompt you for both a starting and ending date. All of the deliveries between the two dates would be listed.
This would not work so well with blank fields, as there is no date for the query to find.

 

[rainman89]

or you could do

between date() and dateadd("d", -21, date()) since its a set incriment and then you can do

between date() and dateadd("d", -21, date()) OR isnull(deliveryDate) to get all undelivered

 

[hardrock]

re:

Between Date() And DateAdd("d",-7,Date()) Or Is Null

Worked fine chaps. Many thanks