Using a query to limit both date AND time range on a report

[Gabe]

I currently am using a query as the record source for a report that I run on
a regular basis. The particular report that I want generated currently uses
the Criteria of the query for and the Between [startdate] and [enddate]
commands for me to determine the date range that I want to show. I want the
report specifically to show the after hours ( 5 PM to 7 AM ) activity that my
employees are logging into the database. (for example, I generate a report to
tell me what happened the night of 3/14 [or early morning 3/15], so when it
promts me for the dates, I enter start date as 3/14 and end date as 3/15. If
I just have the dates in, then it is going to show me stuff from the night I
desire, plus everything that happened early morning the night before and late
evening the night after, which is just too much information, I just want that
one night from 5:00 PM to 7:00 AM). What I have tried so far: On the SAME
record source query I tried to enter in between [starttime] and [endtime]
under the time criteria, and when the report generates, it shows nothing. I
also, under the criteria box of the record source query, tried to enter the

#5:00:00 PM# And <#7:00:00 AM# command, and again, the report generates

blank. NOTE: the time frame that I am trying to filter is all under the exact
same entry (I do not have a start of activity field and an end of activity
field, I have one single time field which records the time that the activity
is recorded). It seems to me like this should logically do what I want it to,
I am ASSUMING the reason I am having problems is because it doesn't like the
fact that I am limiting it to a later than a late time, but earlier than an
early time, then spreading it out accross two days. Please help, thanks

 

[Allen Browne]

Try entering this in the Criteria under your date/time field:
Between DateAdd(17, "h", [StartDate]) And DateAdd(5, "h", [EndDate])

Assuming your field has the date and time in the one field, that expression
selects the records from 1700 on the start date, until 0500 on the end date.
I think that's what you need?

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I currently am using a query as the record source for a report that I run
on
a regular basis. The particular report that I want generated currently
uses
the Criteria of the query for and the Between [startdate] and [enddate]
commands for me to determine the date range that I want to show. I want
the
report specifically to show the after hours ( 5 PM to 7 AM ) activity that
my
employees are logging into the database. (for example, I generate a report
to
tell me what happened the night of 3/14 [or early morning 3/15], so when
it
promts me for the dates, I enter start date as 3/14 and end date as 3/15.
If
I just have the dates in, then it is going to show me stuff from the night
I
desire, plus everything that happened early morning the night before and
late
evening the night after, which is just too much information, I just want
that
one night from 5:00 PM to 7:00 AM). What I have tried so far: On the SAME
record source query I tried to enter in between [starttime] and [endtime]
under the time criteria, and when the report generates, it shows nothing.
I
also, under the criteria box of the record source query, tried to enter
the

#5:00:00 PM# And <#7:00:00 AM# command, and again, the report generates

blank. NOTE: the time frame that I am trying to filter is all under the
exact
same entry (I do not have a start of activity field and an end of activity
field, I have one single time field which records the time that the
activity
is recorded). It seems to me like this should logically do what I want it
to,
I am ASSUMING the reason I am having problems is because it doesn't like
the
fact that I am limiting it to a later than a late time, but earlier than
an
early time, then spreading it out accross two days. Please help, thanks

 

[Gabe]

I tried this and got an error message: expression is typed incorrectly or is
too complex to evaluate. ...a numeric expression may contain too many
complicated elements. try simplifying...

The time and date are NOT recorded in the same field for the record. I
think that might be the reason this is not working. They are in two
different fields, and it needs to remain that way for various other purposes
in the database, even though it might make it easier to solve this problem.

Try entering this in the Criteria under your date/time field:
Between DateAdd(17, "h", [StartDate]) And DateAdd(5, "h", [EndDate])

Assuming your field has the date and time in the one field, that expression
selects the records from 1700 on the start date, until 0500 on the end date.
I think that's what you need?

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I currently am using a query as the record source for a report that I run
on
a regular basis. The particular report that I want generated currently
uses
the Criteria of the query for and the Between [startdate] and [enddate]
commands for me to determine the date range that I want to show. I want
the
report specifically to show the after hours ( 5 PM to 7 AM ) activity that
my
employees are logging into the database. (for example, I generate a report
to
tell me what happened the night of 3/14 [or early morning 3/15], so when
it
promts me for the dates, I enter start date as 3/14 and end date as 3/15.
If
I just have the dates in, then it is going to show me stuff from the night
I
desire, plus everything that happened early morning the night before and
late
evening the night after, which is just too much information, I just want
that
one night from 5:00 PM to 7:00 AM). What I have tried so far: On the SAME
record source query I tried to enter in between [starttime] and [endtime]
under the time criteria, and when the report generates, it shows nothing.
I
also, under the criteria box of the record source query, tried to enter
the

#5:00:00 PM# And <#7:00:00 AM# command, and again, the report generates

blank. NOTE: the time frame that I am trying to filter is all under the
exact
same entry (I do not have a start of activity field and an end of activity
field, I have one single time field which records the time that the
activity
is recorded). It seems to me like this should logically do what I want it
to,
I am ASSUMING the reason I am having problems is because it doesn't like
the
fact that I am limiting it to a later than a late time, but earlier than
an
early time, then spreading it out accross two days. Please help, thanks

 

[Allen Browne]

The "too complex" means JET can't understand the data.
One common example is that the data types are wrong.

Suggestions:
1. Make sure your date and time fields are actually Date/Time fields in
table design (not Text fields.)

2. Declare the parameters.
In query design choose Parameters on the Query menu.
Access opens a dialog.
Enter 2 rows:
[StartDate] Date/Time
[EndDate} Date/Time

Now you need to combine your date and time fields in the query to get the
results you want. Type an expression like this into the Field row:
CVDate([MyDate] + [MyTime])
substituting the names of your date field and your time field inside the
square brackets. Then move the criteria under this field.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I tried this and got an error message: expression is typed incorrectly or
is
too complex to evaluate. ...a numeric expression may contain too many
complicated elements. try simplifying...

The time and date are NOT recorded in the same field for the record. I
think that might be the reason this is not working. They are in two
different fields, and it needs to remain that way for various other
purposes
in the database, even though it might make it easier to solve this
problem.

Try entering this in the Criteria under your date/time field:
Between DateAdd(17, "h", [StartDate]) And DateAdd(5, "h", [EndDate])

Assuming your field has the date and time in the one field, that
expression
selects the records from 1700 on the start date, until 0500 on the end
date.
I think that's what you need?

I currently am using a query as the record source for a report that I
run
on
a regular basis. The particular report that I want generated currently
uses
the Criteria of the query for and the Between [startdate] and [enddate]
commands for me to determine the date range that I want to show. I want
the
report specifically to show the after hours ( 5 PM to 7 AM ) activity
that
my
employees are logging into the database. (for example, I generate a
report
to
tell me what happened the night of 3/14 [or early morning 3/15], so
when
it
promts me for the dates, I enter start date as 3/14 and end date as
3/15.
If
I just have the dates in, then it is going to show me stuff from the
night
I
desire, plus everything that happened early morning the night before
and
late
evening the night after, which is just too much information, I just
want
that
one night from 5:00 PM to 7:00 AM). What I have tried so far: On the
SAME
record source query I tried to enter in between [starttime] and
[endtime]
under the time criteria, and when the report generates, it shows
nothing.
I
also, under the criteria box of the record source query, tried to enter
the
#5:00:00 PM# And <#7:00:00 AM# command, and again, the report generates
blank. NOTE: the time frame that I am trying to filter is all under the
exact
same entry (I do not have a start of activity field and an end of
activity
field, I have one single time field which records the time that the
activity
is recorded). It seems to me like this should logically do what I want
it
to,
I am ASSUMING the reason I am having problems is because it doesn't
like
the
fact that I am limiting it to a later than a late time, but earlier
than
an
early time, then spreading it out accross two days. Please help, thanks

 

[John Spencer]

UMMM! I think you reversed the arguments in the dateAdd function.

Should not that be
BETWEEN DateAdd("h",17,[StartDate]) and DateAdd("h",5,[EndDate])

Although I think what the poster wants is to split out the time using the
time value, so the poster only gets activities between the hours of 17:00 to
midnight and Midnight to 05:00:00

WHERE TheDateTimeField Between [StartDate] and [EndDate]
And TimeValue(DateAdd("h",-8,TheDateTimeField)) Between #09:00:00# AND
#23:00:00#

Alternative criteria
WHERE TheDateTimeField Between [StartDate] and [EndDate]
And (TimeValue(TheDateTimeField) Between #17:00:00# AND #24:00:00#
OR TimeValue(TheDateTimeField) Between #00:00:00# AND #05:00:00#)
--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Try entering this in the Criteria under your date/time field:
Between DateAdd(17, "h", [StartDate]) And DateAdd(5, "h", [EndDate])

Assuming your field has the date and time in the one field, that
expression selects the records from 1700 on the start date, until 0500 on
the end date. I think that's what you need?

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I currently am using a query as the record source for a report that I run
on
a regular basis. The particular report that I want generated currently
uses
the Criteria of the query for and the Between [startdate] and [enddate]
commands for me to determine the date range that I want to show. I want
the
report specifically to show the after hours ( 5 PM to 7 AM ) activity
that my
employees are logging into the database. (for example, I generate a
report to
tell me what happened the night of 3/14 [or early morning 3/15], so when
it
promts me for the dates, I enter start date as 3/14 and end date as 3/15.
If
I just have the dates in, then it is going to show me stuff from the
night I
desire, plus everything that happened early morning the night before and
late
evening the night after, which is just too much information, I just want
that
one night from 5:00 PM to 7:00 AM). What I have tried so far: On the SAME
record source query I tried to enter in between [starttime] and [endtime]
under the time criteria, and when the report generates, it shows nothing.
I
also, under the criteria box of the record source query, tried to enter
the

#5:00:00 PM# And <#7:00:00 AM# command, and again, the report generates

blank. NOTE: the time frame that I am trying to filter is all under the
exact
same entry (I do not have a start of activity field and an end of
activity
field, I have one single time field which records the time that the
activity
is recorded). It seems to me like this should logically do what I want it
to,
I am ASSUMING the reason I am having problems is because it doesn't like
the
fact that I am limiting it to a later than a late time, but earlier than
an
early time, then spreading it out accross two days. Please help, thanks

 

[Allen Browne]

Thanks for picking up on the reversed arguments.

You may have also interpreted the question correctly.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

UMMM! I think you reversed the arguments in the dateAdd function.

Should not that be
BETWEEN DateAdd("h",17,[StartDate]) and DateAdd("h",5,[EndDate])

Although I think what the poster wants is to split out the time using the
time value, so the poster only gets activities between the hours of 17:00
to midnight and Midnight to 05:00:00

WHERE TheDateTimeField Between [StartDate] and [EndDate]
And TimeValue(DateAdd("h",-8,TheDateTimeField)) Between #09:00:00# AND
#23:00:00#

Alternative criteria
WHERE TheDateTimeField Between [StartDate] and [EndDate]
And (TimeValue(TheDateTimeField) Between #17:00:00# AND #24:00:00#
OR TimeValue(TheDateTimeField) Between #00:00:00# AND #05:00:00#)
--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
.

Try entering this in the Criteria under your date/time field:
Between DateAdd(17, "h", [StartDate]) And DateAdd(5, "h", [EndDate])

Assuming your field has the date and time in the one field, that
expression selects the records from 1700 on the start date, until 0500 on
the end date. I think that's what you need?

I currently am using a query as the record source for a report that I run
on
a regular basis. The particular report that I want generated currently
uses
the Criteria of the query for and the Between [startdate] and [enddate]
commands for me to determine the date range that I want to show. I want
the
report specifically to show the after hours ( 5 PM to 7 AM ) activity
that my
employees are logging into the database. (for example, I generate a
report to
tell me what happened the night of 3/14 [or early morning 3/15], so when
it
promts me for the dates, I enter start date as 3/14 and end date as
3/15. If
I just have the dates in, then it is going to show me stuff from the
night I
desire, plus everything that happened early morning the night before and
late
evening the night after, which is just too much information, I just want
that
one night from 5:00 PM to 7:00 AM). What I have tried so far: On the
SAME
record source query I tried to enter in between [starttime] and
[endtime]
under the time criteria, and when the report generates, it shows
nothing. I
also, under the criteria box of the record source query, tried to enter
the
#5:00:00 PM# And <#7:00:00 AM# command, and again, the report generates
blank. NOTE: the time frame that I am trying to filter is all under the
exact
same entry (I do not have a start of activity field and an end of
activity
field, I have one single time field which records the time that the
activity
is recorded). It seems to me like this should logically do what I want
it to,
I am ASSUMING the reason I am having problems is because it doesn't like
the
fact that I am limiting it to a later than a late time, but earlier than
an
early time, then spreading it out accross two days. Please help, thanks