Date Filter

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

re: Date Filter

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" <nathanbloomfield@hotmail.com> wrote in message
news:4bbc1902.0312222103.5be0ae62@posting.google.c om...[color=blue]
> 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[/color]

re: Date Filter

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" <nathanbloomfield@hotmail.com> wrote in message
news:4bbc1902.0312222103.5be0ae62@posting.google.c om...[color=blue]
> 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[/color]

re: Date Filter

> The date/time type in Access treats[color=blue]
> #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\#") & ")"
>[/color]




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!