Your start and end fields need to include the date. Date/time fields are not well understood. They are not strings, they are double precision numbers and they are intended to hold a point in time. That point is a date and the exact time of day. You may choose to store only the date portion or only the time portion but if you need to do arithmetic with the value, I strongly suggest that you use the field as it is intended. You can acutally add the date field and the time field if they are currently separate.
DateDiff("h", [StartTime], [EndTime]) can't return the correct time in hours.
For example, DateDiff("h",#12/26/2004 8:58:0#,#12/26/2004 9:0:0#) will return 1 hour (i.e. 9 - 8) though actually only two minutes has elapsed.
The correct expression to use is ([EndTime]-[StartTime])*24 as [EndTime]-[StartTime] remains a date/time value and the unit of a date/time value is day and each day has 24 hours.
As an illustration, I have attached a sample database with a table "tblData" :
Code:
[b]ID PageCount StartTime EndTime[/b]
001 60 8:00:00 AM 9:00:00 AM
001 30 8:30:00 AM 9:00:00 AM
002 61 8:00:00 AM 9:01:00 AM
002 33.5 8:30:00 AM 9:03:30 AM
003 61 26/12/2004 11:30:00 PM 27/12/2004 12:31:00 AM
003 1473.5 26/12/2004 8:30:00 AM 27/12/2004 9:03:30 AM
004 3000 26/12/2004 8:30:00 AM 28/12/2004 9:23:26 AM
and two queries :
"Pages per hour"
SELECT ID, PageCount, StartTime, EndTime,
Int([EndTime]-[StartTime])*24+DatePart("h",[EndTime]-[StartTime]) & " hr " & Format([EndTime]-[StartTime],"n"" min ""s"" sec""") AS [Time],
Round(([EndTime]-[StartTime])*24,4) AS [Total Hours],
Round([PageCount]/[Total Hours],2) AS [Pages Per Hour]
FROM tblData;
"Average Pages per hour"
SELECT [ID], Sum([PageCount]) AS [Page Count],
Int(Sum([EndTime]-[StartTime]))*24+DatePart("h",Sum([EndTime]-[StartTime])) & " hr " & Format(Sum([EndTime]-[StartTime]),"n"" min ""s"" sec""") AS [Total Time],
Round(Sum([EndTime]-[StartTime])*24,4) AS [Total Hours],
Round([Page Count]/[Total Hours],2) AS [Average Pages Per Hour]
FROM tblData
GROUP BY [ID];
When the queries are run, they will return the results :
Code:
[b]ID PageCount StartTime EndTime Time Total Pages
Hours Per Hour[/b]
001 60 ...... ...... 1 hr 0 min 0 sec 1 60
001 30 ...... ...... 0 hr 30 min 0 sec 0.5 60
002 61 ...... ...... 1 hr 1 min 0 sec 1.0167 60
002 33.5 ...... ...... 0 hr 33 min 30 sec 0.5583 60
003 61 ...... ...... 1 hr 1 min 0 sec 1.0167 60
003 1473.5 ...... ...... 24 hr 33 min 30 sec 24.5583 60
004 3000 ...... ...... 48 hr 53 min 26 sec 48.8906 61.36
[b]ID Page Count Total Time Total Hours Average Pages
Per Hour[/b]
001 90 1 hr 30 min 0 sec 1.5 60
002 94.5 1 hr 34 min 30 sec 1.575 60
003 1534.5 25 hr 34 min 30 sec 25.575 60
004 3000 48 hr 53 min 26 sec 48.8906 61.36
Note: If the EndTime crosses mid-night, the dates must also be entered in the two time fields (as shown in the last three records in the table.)
If the dates are stored in two separate fields in the table, you can use the following expression to return the time in Hours:-
(([EndDate]+[EndTime]) - ([StartDate]+[StartTime]))*24 .
