give option to use date range or all dates

[Guest]

I have a database with a date as a field. I would like to have the option of
either entering a date range to select records in a query or allow users to
select all the records without having to enter 01/01/01 and 12/12/99 as a
beginning and ending date range. Any suggestions?

 

[Douglas J. Steele]

If you're prompting them using a parameter query (or referring to text boxes
on an open form), try changing your SQL from WHERE DateField Between
Nz([StartDate], #01/01/01#) And Nz([EndDate], #12/12/99#)

 

[John Vinson]

I have a database with a date as a field. I would like to have the option of
either entering a date range to select records in a query or allow users to
select all the records without having to enter 01/01/01 and 12/12/99 as a
beginning and ending date range. Any suggestions?

Consider using a criterion such as

([datefield] >= [Enter start date:] OR [Enter start date:] IS NULL)
AND
([datefield] <= [Enter end date:] OR [Enter end date:] IS NULL)

If the user enters just the start date they'll see all records that
date or later; just the end date will give all records that date or
earlier; entering neither will give all records.

 

[John Vinson]

without having to enter 01/01/01 and 12/12/99 as a
beginning and ending date range

Hm. Check your logic... because of the way Access handles two-digit
years, this corresponds to the range from 01/01/2001 to 12/12/1999. If
you want 2099, or for that matter 9999, you need the four digit year!