Date Range

[Guest]

Tp pull a monthly report I have under criteria of Query: "Between
[BeginningDate] And [EndingDate]". When Prompted I can enter Beginning, then
End Date. I would like to also include an option where I can leave the
beginning date blank and End date blank to pull all records. I have a
similar Query that pulls all parts of CLient Names: Like "*" & [Enter Client
Name, Leave Blank for All] & "*".... When I am trying to get this to work for
my dates, I either Pull All records no matter the date ranges I enter, Or, I
pull no records....
I would also like to know if I can prompt it to ONLY enter a month, Jan or
01 for January. Currently the Date is default date (), formated
(01/31/2007).

 

[John Spencer]

Assuming that your date field ALWAYS has a date then one way to handle this
is to force an out-of-range beginning and ending date if the prompt is left
blank.

Between NZ([BeginningDate],#1/1/1600#) and NZ([EndDate],#12/31/2999#)

If your date field is null (blank) then you won't get those records back.
There are solutions for that also. Simplest is to use a calculated field to
search against while you display the actual date field. The problem with
this approach is that it can be slow with a large number of records.

Field: SearchThis: Nz([YourDateField],#12/31/2999#)
Criteria: Between NZ([BeginningDate],#1/1/1600#) and
NZ([EndDate],#12/31/2999#)

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..