Using date range in query criteria

  • Thread starter Thread starter Kathi
  • Start date Start date
K

Kathi

I have a form that gathers a date range that is then used
in a Between statement in my query. But, I would like to
include all records if the date is not entered. This is
sort of the Like * criteria expression, except with
dates. Is there such a thing?
Thanks, in advance, for your help.
 
Between NZ([Forms]![MyForm]![MyStartDate], #1700-01-01#) and
NZ([Forms]![MyForm]![MyEndDate], #9999-12-31#)
Modify the dates in the above to a range that won't possibly exclude any of
your records.
 
To create a good interface for this, use either a form or a report. You can
then enter the limiting dates on the form, and filter the form/report to
just the matches.

This link provides an example using two text boxes named txtStartDate and
txtEndDate:
http://members.iinet.net.au/~allenbrowne/casu-08.html

If txtEndDate is left blank, it shows all records from the starting date
onwards. If txtStartDate is left blank, it shows all records up to the end
date. If both are left blank, it shows all records. If both are entered, it
shows only records between the two dates (both inclusive).

The example ends up applying the string to an OpenReport action. If you
wanted to use it to filter a form, you would use:
Me.Filter = strWhere
Me.FilterOn = True
 
Thanks so much. I have been struggling with dates since
Access didn't have a version number after it. I didn't
know that NZ (which I use for numbers) would work for
dates, also. You have made my life Soooooo much easier.
This is a traditional query, so I wasn't using code to
open the report, so your suggestion works fabulously!
Thanks again, it's so nice to have great support people
like you cruising the newgroups and helping!
Kathi
-----Original Message-----
Between NZ([Forms]![MyForm]![MyStartDate], #1700-01-01#) and
NZ([Forms]![MyForm]![MyEndDate], #9999-12-31#)
Modify the dates in the above to a range that won't possibly exclude any of
your records.
I have a form that gathers a date range that is then used
in a Between statement in my query. But, I would like to
include all records if the date is not entered. This is
sort of the Like * criteria expression, except with
dates. Is there such a thing?
Thanks, in advance, for your help.
Click to expand...

--
Joan Wild
Microsoft Access MVP


.
Click to expand...
 
Thanks so much for your help with the date struggle. I
have been thinking bad thoughts about MS with dates since
before Access had a version number after it. In this
case, I am writing the database to hand it over to someone
who is not very familiar with Access, let alone its
programming option, and so am trying to stick as much with
queries as I can. But...I write databases for others,
also, and your code has been added to my "tricks" list.
Thanks so much...its wonderful to have you all watching
over us. Kathi
 
Back
Top