Access Query - Date Range Or All Records

I have an access query with a date range

I entered the first query criteria as Between [Start Date] And [End Date] - OK

I entered a 2nd query criteria as Like "*" - NOK

What i need is to return all records if no dates are entered.

Any help most welome.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.
Gustav BrockCIOCommented:
Or simply:

Between Nz([Start Date], [YourDateField]) And Nz([End Date], [YourDateField])


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
CEOs need to know what they should worry about

Nearly every week during the past few years has featured a headline about the latest data breach, malware attack, ransomware demand, or unrecoverable corporate data loss. Those stories are frequently followed by news that the CEOs at those companies were forced to resign.

SweetingAAuthor Commented:
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.
SweetingAAuthor Commented:
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
Gustav BrockCIOCommented:
You are correct about the data types and the behaviour.

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).


Ah- I see... the detailed comment is in the "close request Pending" box.  I need more caffeine...
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.