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

Date Filter

P: n/a
I am having difficulty filtering dates for a report. I have written
the following code on a popup form which works with regular date
fields , however, I am now trying to apply the same concept to a field
which is formatted as date/time i.e. now()

DoCmd.OpenReport "rptOpenLabels", acViewPreview, , "[MyDate] Between
#" & Format(Me![BeginDate], "mm/dd/yy") & "# and #" &
Format(Me![EndDate], "mm/dd/yy") & "#"

The code above does not take into account the 00:00 to 23:59 and must
default to 23:59 as the filter 22/12/03 to 23/12/03 will result in
figures for 23/12 but 23/12/03 to 23/12/03 results in an empty report.

Any help will be much appreciated.

Regards,

Nathan
Nov 12 '05 #1
Share this Question
Share on Google+
3 Replies

P: n/a
TC
Maybe try just adding the relevant cutoff times to the string:

... BeginDate], "mm/dd/yy") & " 00:00:00# and ...
^^^^^^^

Also, personally, I would >defunitely< use a 4-digit year format (yyyy). Why
take the chance of the 2-digit format being misinterpreted? (eg. is #1/1/50#
in1850, 1950, 2050, ...)

HTH,
TC
"Nathan Bloomfield" <na**************@hotmail.com> wrote in message
news:4b**************************@posting.google.c om...
I am having difficulty filtering dates for a report. I have written
the following code on a popup form which works with regular date
fields , however, I am now trying to apply the same concept to a field
which is formatted as date/time i.e. now()

DoCmd.OpenReport "rptOpenLabels", acViewPreview, , "[MyDate] Between
#" & Format(Me![BeginDate], "mm/dd/yy") & "# and #" &
Format(Me![EndDate], "mm/dd/yy") & "#"

The code above does not take into account the 00:00 to 23:59 and must
default to 23:59 as the filter 22/12/03 to 23/12/03 will result in
figures for 23/12 but 23/12/03 to 23/12/03 results in an empty report.

Any help will be much appreciated.

Regards,

Nathan

Nov 12 '05 #2

P: n/a
The date/time type in Access treats
#12/12/2003 10:00:00#
to be *after*
#12/12/2003#.
So - as you found - if you filter to the date, it does not include any times
on that date.

Try asking for records up to and including the last second of the day:
"[MyDate] Between #" & Format(Me![BeginDate], "mm\/dd\/yyyy") & _
"# And #" & Format(Me![EndDate], "mm\/dd\/yyyy") & " 23:59:59#"
Alternatively, ask for records that are less than the next day:
"([MyDate] >= " & Format(Me![BeginDate], "\#mm\/dd\/yyyy\#") & _
") AND ([MyDate] < " & Format(Me![EndDate] + 1, "\#mm\/dd\/yyyy\#") & ")"

These solutions are more efficient than using DateSerial() on the field
value. That executes a function call at every row and prevents JET from
using an index on the date field.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Nathan Bloomfield" <na**************@hotmail.com> wrote in message
news:4b**************************@posting.google.c om...
I am having difficulty filtering dates for a report. I have written
the following code on a popup form which works with regular date
fields , however, I am now trying to apply the same concept to a field
which is formatted as date/time i.e. now()

DoCmd.OpenReport "rptOpenLabels", acViewPreview, , "[MyDate] Between
#" & Format(Me![BeginDate], "mm/dd/yy") & "# and #" &
Format(Me![EndDate], "mm/dd/yy") & "#"

The code above does not take into account the 00:00 to 23:59 and must
default to 23:59 as the filter 22/12/03 to 23/12/03 will result in
figures for 23/12 but 23/12/03 to 23/12/03 results in an empty report.

Any help will be much appreciated.

Regards,

Nathan

Nov 12 '05 #3

P: n/a
> The date/time type in Access treats
#12/12/2003 10:00:00#
to be *after*
#12/12/2003#.
So - as you found - if you filter to the date, it does not include any times
on that date.

Try asking for records up to and including the last second of the day:
"[MyDate] Between #" & Format(Me![BeginDate], "mm\/dd\/yyyy") & _
"# And #" & Format(Me![EndDate], "mm\/dd\/yyyy") & " 23:59:59#"
Alternatively, ask for records that are less than the next day:
"([MyDate] >= " & Format(Me![BeginDate], "\#mm\/dd\/yyyy\#") & _
") AND ([MyDate] < " & Format(Me![EndDate] + 1, "\#mm\/dd\/yyyy\#") & ")"



It seems that my problem is the reverse of suggestions which have been
made so far.

i.e. if I want to filter for 24/12/03 then I have to filter using
Begindate: 23/12/03 & EndDate: 24/12/03 (this works). Filtering
using Begindate: 24/12/03 & EndDate: 25/12/03 results in an empty
report.

Note that I am using Australian standard date format dd/mm/yy - the US
standard is required in the code for some reason and works perfectly
for normal date fields in other queries I have setup - just not the
date with timestamp.

It seems as though Access is treating anything > 24/12/03 00:00:00 as
the following day.

So I have adjusted one of the suggestions above to the following:

"([MyDate] >= " & Format(Me![BeginDate] - 1, "\#mm\/dd\/yy\#") & _
") AND ([MyDate] < " & Format(Me![EndDate], "\#mm\/dd\/yy\#") & ")"

Is this a practical solution? Will there be any dates which may be
treated differently in the future?
Thanks for your help peoples!
Nov 12 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.