Yet another date range question

M

M.Anderson

I have a legacy database in Access (presently 2003), containing call logs.
There is a query that returns all calls that occurred within a specified time
frame -- however, I've just now noticed that it appears to contain an error,
which I'd like to fix: it always returns a range that's one day smaller than
whatever you asked for!

For example, if you specify 2/1/2006 to 2/3/2006, you will only get records
up to 2/2/2006. In order to get anything from 2/3, you need to ask for 2/4!

I believe that this is due to a mismatch between date formats -- one table
is "General Date" while another is "Short Date". For my purposes, the extra
information provided by the "General Date" format is irrelevant to this query.

However, I have limited rights on this database, and cannot easily access
the tables themselves, but I *can* edit the queries. Is there a way to write
a query to accurately return records from a date range without modifying the
tables & all the historical data they contain? I'd appreciate any thoughts
you folks might have!

The SQL I've got to work with is below:
-----begin-----
SELECT Count(qry_ClosedCalls.Call_ID) AS CountOfCall_ID,
Sum(IIf(qry_ClosedCalls!TrueHoursElapsed<24,1,0)) AS [24 or less], [24 or
less]/[CountOfCall_ID] AS [24 or less %],
Sum(IIf(qry_ClosedCalls!TrueHoursElapsed Between 25 And 48,1,0)) AS [24-48],
[24-48]/[CountOfCall_ID] AS [24-48 %],
Sum(IIf(qry_ClosedCalls!TrueHoursElapsed>48,1,0)) AS [48+],
[48+]/[CountOfCall_ID] AS [48+ %], Avg(qry_ClosedCalls.TrueHoursElapsed) AS
AvgOfTrueHoursElapsed

FROM qry_ClosedCalls, tbl_DateRange

WHERE (((qry_ClosedCalls.Call_Date)<[tbl_DateRange]![End_Date] And
(qry_ClosedCalls.Call_Date)>[tbl_DateRange]![Begin_Date]));
-----end-----
 
R

Rick B

I believe this is because you are dealing with a date and TIME. If you do
not specify a time, then midnight is assumed. Since you are asking for all
records through midnight of 02/03/06 in your example, you will not get any
records from after midnight that morning.
 
M

M.Anderson

That seems eminently reasonable; is there a way to drop the time information?
In other words, query a "General Date" field AS IF it were a "Short Date"
field?

Rick B said:
I believe this is because you are dealing with a date and TIME. If you do
not specify a time, then midnight is assumed. Since you are asking for all
records through midnight of 02/03/06 in your example, you will not get any
records from after midnight that morning.



--
Rick B



M.Anderson said:
I have a legacy database in Access (presently 2003), containing call logs.
There is a query that returns all calls that occurred within a specified
time
frame -- however, I've just now noticed that it appears to contain an
error,
which I'd like to fix: it always returns a range that's one day smaller
than
whatever you asked for!

For example, if you specify 2/1/2006 to 2/3/2006, you will only get
records
up to 2/2/2006. In order to get anything from 2/3, you need to ask for
2/4!

I believe that this is due to a mismatch between date formats -- one table
is "General Date" while another is "Short Date". For my purposes, the
extra
information provided by the "General Date" format is irrelevant to this
query.

However, I have limited rights on this database, and cannot easily access
the tables themselves, but I *can* edit the queries. Is there a way to
write
a query to accurately return records from a date range without modifying
the
tables & all the historical data they contain? I'd appreciate any thoughts
you folks might have!

The SQL I've got to work with is below:
-----begin-----
SELECT Count(qry_ClosedCalls.Call_ID) AS CountOfCall_ID,
Sum(IIf(qry_ClosedCalls!TrueHoursElapsed<24,1,0)) AS [24 or less], [24 or
less]/[CountOfCall_ID] AS [24 or less %],
Sum(IIf(qry_ClosedCalls!TrueHoursElapsed Between 25 And 48,1,0)) AS
[24-48],
[24-48]/[CountOfCall_ID] AS [24-48 %],
Sum(IIf(qry_ClosedCalls!TrueHoursElapsed>48,1,0)) AS [48+],
[48+]/[CountOfCall_ID] AS [48+ %], Avg(qry_ClosedCalls.TrueHoursElapsed)
AS
AvgOfTrueHoursElapsed

FROM qry_ClosedCalls, tbl_DateRange

WHERE (((qry_ClosedCalls.Call_Date)<[tbl_DateRange]![End_Date] And
(qry_ClosedCalls.Call_Date)>[tbl_DateRange]![Begin_Date]));
-----end-----
Click to expand...
Click to expand...
 
J

John Spencer

Use DateValue to strip off the time from the field.

Or perhaps more efficiently change the criteria to add in the extra day

WHERE qry_ClosedCalls.Call_Date< DateAdd("d",1,[tbl_DateRange]![End_Date]) And
qry_ClosedCalls.Call_Date>[tbl_DateRange]![Begin_Date]


WHERE DateValue(qry_ClosedCalls.Call_Date) < [tbl_DateRange]![End_Date] And
DateValue(qry_ClosedCalls.Call_Date)>[tbl_DateRange]![Begin_Date]

M.Anderson said:
That seems eminently reasonable; is there a way to drop the time information?
In other words, query a "General Date" field AS IF it were a "Short Date"
field?

Rick B said:
I believe this is because you are dealing with a date and TIME. If you do
not specify a time, then midnight is assumed. Since you are asking for all
records through midnight of 02/03/06 in your example, you will not get any
records from after midnight that morning.



--
Rick B



M.Anderson said:
I have a legacy database in Access (presently 2003), containing call logs.
There is a query that returns all calls that occurred within a specified
time
frame -- however, I've just now noticed that it appears to contain an
error,
which I'd like to fix: it always returns a range that's one day smaller
than
whatever you asked for!

For example, if you specify 2/1/2006 to 2/3/2006, you will only get
records
up to 2/2/2006. In order to get anything from 2/3, you need to ask for
2/4!

I believe that this is due to a mismatch between date formats -- one table
is "General Date" while another is "Short Date". For my purposes, the
extra
information provided by the "General Date" format is irrelevant to this
query.

However, I have limited rights on this database, and cannot easily access
the tables themselves, but I *can* edit the queries. Is there a way to
write
a query to accurately return records from a date range without modifying
the
tables & all the historical data they contain? I'd appreciate any thoughts
you folks might have!

The SQL I've got to work with is below:
-----begin-----
SELECT Count(qry_ClosedCalls.Call_ID) AS CountOfCall_ID,
Sum(IIf(qry_ClosedCalls!TrueHoursElapsed<24,1,0)) AS [24 or less], [24 or
less]/[CountOfCall_ID] AS [24 or less %],
Sum(IIf(qry_ClosedCalls!TrueHoursElapsed Between 25 And 48,1,0)) AS
[24-48],
[24-48]/[CountOfCall_ID] AS [24-48 %],
Sum(IIf(qry_ClosedCalls!TrueHoursElapsed>48,1,0)) AS [48+],
[48+]/[CountOfCall_ID] AS [48+ %], Avg(qry_ClosedCalls.TrueHoursElapsed)
AS
AvgOfTrueHoursElapsed

FROM qry_ClosedCalls, tbl_DateRange

WHERE (((qry_ClosedCalls.Call_Date)<[tbl_DateRange]![End_Date] And
(qry_ClosedCalls.Call_Date)>[tbl_DateRange]![Begin_Date]));
-----end-----
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

SUMPRODUCT where between 2 dates 3
Can I convert text to columns in a query? 0
using an IIF with a field value to update 3
Average with multiple conditions 4
Specified Expression/Aggreagate Function 5
Another Query Modification Question 6
Addition to SQL query found 0
Query acting up 0

Top