Access Query - Date Range Or All Records

Select from yourTable where ([Enter a date] Between [Start Date] And [End Date]) or
(nz([Start Date], "") = "" and  nz([End Date], "") = "")

This is another method:

Between NZ([Start Date], #1/1/1900#) And NZ([End Date], #1/1/2199#)


the 1/1/1900 and 1/1/2199 are arbitrary unrealistic early and late dates that you can reasonably expect to NOT occur in your data.

With a blank start date, it will return all dates up to and including the End Date.

With a blank end date, it will return all dates including and any time after the start Date.

WIth both the end and start dates blank, it will return all dates.

The Cactus_Data solution works perfectly, the MBzip solution returned no records when the date fields were left blank, maybe there is another problem there.

Thanks for all your support.

I've requested that this question be closed as follows:

Accepted answer: 400 points for cactus_data's comment #a39538891
Assisted answer: 100 points for mbizup's comment #a39538467
Assisted answer: 0 points for SweetingA's comment #a39539195

for the following reason:

I think the only reason the MBZIP solution never worked completely was becuase the data is not true dates, they are numbers which have been sorted into date format - maybe i'm wrong but i think thats the issue - never had time to prove it.

The cactus solution works perfectly as the formatting is less relevant

You are correct about the data types and the behaviour.

/gustav

I think something about the datatype was lost/edited out of comment http:#a39539195 :)

But yes, if you are storing your dates as text, you will see the behaviour you described.

With your dates stored as text, you'd have to format any hard-coded dates in your criteria correctly and delimit them as strings (using quotes instead of #'s).

EDIT:

Ah- I see... the detailed comment is in the "close request Pending" box.  I need more caffeine...