By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,080 Members | 1,817 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,080 IT Pros & Developers. It's quick & easy.

getting proper dates covered for reports

P: 47
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:

Expand|Select|Wrap|Line Numbers
  1.  >=[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
Mar 18 '08 #1
Share this Question
Share on Google+
4 Replies

Expert Mod 2.5K+
P: 2,545
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:
Expand|Select|Wrap|Line Numbers
  1.  >=#[Forms]![frmReportMenu]![txtStartDate]# And <=#[Forms]![frmReportMenu]![txtEndDate]#
  2. '  or
  3. Between #[Forms]![frmReportMenu]![txtStartDate]# And #[Forms]![frmReportMenu]![txtEndDate]#
Mar 18 '08 #2

P: 47
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.

Mar 18 '08 #3

Expert Mod 2.5K+
P: 2,545
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

Expand|Select|Wrap|Line Numbers
  1. Between Cdate(forms![formname]![controlname]) AND Cdate(forms![formname]![controlname])
  2. ' or
  3. >= Cdate(forms![formname]![controlname]) AND <= Cdate(forms![formname]![controlname])
Mar 18 '08 #4

P: 47
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.
Mar 18 '08 #5

Post your reply

Sign in to post your reply or Sign up for a free account.