Date range parameter in report query

[FranS37]

I have a report that uses this parameter in a query:

>=nz([forms]![Customer Invoice]![BeginDate]) And <=nz([forms]![Customer Invoice]![EndDate])

When the user enters the same date for BeginDate and EndDate the report produces no results, even if there is data for that date.

In order to produce results for just one date, the user must enter a date range, for example BeginDate = 01/04/04 and EndDate = 01/05/2004 produces data for 01/04/04.

Can I get the formula to allow a BeginDate and EndDate that are the same but still also work on a date range?

Any help is appreciated. Thanks.

 

[WildHare]

DATEs for which you enter only a CALENDAR part (M/D/Y) are automatically set to MIDNIGHT as their time part.

Thus, a begin-date of 1/4/04 is really set to 1/4/04 MIDNIGHT, and an end date of 1/4/04 is really set to 1/4/04 MIDNIGHT, thus the lack of records in the time interval between them....

A Q&D fix for this is in the AFTER UPDATE event of the END DATE, slap a line of code like this:

EndDate = IIF(BeginDate=Enddate, EndDate+1, EndDate)

This, if you enter 1/4/04 for each date, this guy will simply change the 1/4/04 that's in the ENDATE bucket to 1/5/04 (Midnight, don't forget...) and you should be fine. You'll get things between MIDNIGHT on the 4th, and Midnight on the FIFTH...

Jim




Don't be sexist - Broads hate that.
Another free Access forum:

http://www.forumco.com/jmhare

More Access help stuff at

http://www.jmhare.com/wildhare.htm