getting proper dates covered for reports

[akirekab]

I would like some advice on the best way in MS Access, in query builder to get criteria that will include certain days.

something similar to this code:

 >=[Forms]![frmReportMenu]![txtStartDate] And <=[Forms]![frmReportMenu]![txtEndDate]
 

I basically need to be able to run reports from a query that will gather data for all days of the month. Of course I would like to use the Between() function, but it seems there are all sorts of rules that make it not do what the book says.
I can never seem to get the final day included.

Thank yo for any help
Ken

[Stewart Ross]

Hi Ken. Using BETWEEN should work just as well as the above. In both cases you would need to use the '#' signs to tell the query editor that these are date values:

 >=#[Forms]![frmReportMenu]![txtStartDate]# And <=#[Forms]![frmReportMenu]![txtEndDate]#
'  or
Between #[Forms]![frmReportMenu]![txtStartDate]# And #[Forms]![frmReportMenu]![txtEndDate]#
 

-Stewart

[akirekab]

HI and thank you Stewart

Both of the expressions returned an access error
"The expressions you entered returned an invalid date value"

any thoughts on that.

Ken

[Stewart Ross]

Sorry, Ken; the date literals don't work with such form references it seems - but what does work is to use explicit date typecasting using the CDate function. It is of the form

Between Cdate(forms![formname]![controlname]) AND Cdate(forms![formname]![controlname])
' or
>= Cdate(forms![formname]![controlname]) AND <= Cdate(forms![formname]![controlname])

-Stewart

[akirekab]

Hi Stewart,
Well neither of these picks up the last day. Between is supposed to and the other I would have thought would, but some internal stuff keeps it from happening I guess.

In both cases though data from the 31st was left out.

I can't believe that something this well used doesn't have a basic function that works quickly, simply and without fail, as it so important.

I can make it work with adjustments.

Thanks for trying.
Ken