Gustav Brock
gustav at cactus.dk
Fri Nov 14 11:51:36 CST 2003
Hi Bob
Use DateDiff("n", datStart, datStop) to calculate the time difference
in minutes (assuming you are ignoring seconds). Sum those minutes as
your subtotal.
Then you can use a function like this to calculate a pseudo time:
<code>
Function TimeHour(ByVal curHMS As Currency) As Date
' Rounds and converts curHMS (number of hours as decimalnumber).
' Returns a time value, hh:nn:ss.
' Gustav Brock, Cactus Data ApS.
' 1999-08-12.
Dim lngH As Long
Dim lngM As Long
Dim lngS As Long
Dim curR As Currency
' No error handling needed.
On Error Resume Next
' Round to two decimals, and skip number of days if curHMS > 24.
' If day count is needed, omit modulus like this:
' curHMS = (CLng(curHMS * 10000) / 100 * 36) ' Mod 86400
curHMS = (CLng(curHMS * 10000) / 100 * 36) Mod 86400
' Calculate hours, minutes, and seconds.
lngH = Int(curHMS / 3600)
curR = curHMS - (lngH * 3600)
lngM = Int(curR / 60)
curR = curR - (lngM * 60)
lngS = CInt(curR)
' Create time value.
TimeHour = TimeSerial(lngH, lngM, lngS)
End Function
</code>
Finally, format the returned date/time value as you like, say:
strTimeDiff = Format(datTimeDiff, "hh:nn")
or simply specify this format for the textbox in the report.
/gustav
> I cannot figure out how to add time.
> I have a database that tracks responses for emergency services providers.
> Table and report details are below. I'm trying to create a report showing
> each provider's responses, sorted and subtotaled by major class types and
> then minor class types, with the time of each incident shown AND subtotaled.
> I have no problem with the report EXCEPT that I cannot subtotal the time
> (the "Time = " and "Total Time = " fields).
> I suspect that the problem may be that I am using a formatted field
> (TimeDiff), and cannot add the data because they are now string values. If
> anyone can help steer me in a better direction, I would be extremely
> grateful!
> TIA,
> Bob Gajewski
> *************************************************************
> DATABASE DETAILS:
> tblEmployees
> EmployeeID (PK)
> EmployeeName
> tblMajorClass
> MajorClassID (PK)
> MajorClassName
> tblMinorClass
> MinorClassID (PK)
> MinorClassName
> tblIncidents
> IncidentID (PK)
> MajorClassID (FK)
> (has "Call" or "Drill")
> MinorClassID (FK)
> (for "Call", has "Fire" or "EMS")
> (for "Drill", has "Internal" or "External")
> DispatchTime
> InServiceTime
> tblResponses
> ResponseID (PK)
> IncidentID (FK)
> EmployeeID (FK)
> The report (rptEmployeeResponse) has the following:
> Sorting & Grouping:
> EmployeeName (Ascending)
> Group Header = Yes
> Group Footer = Yes
> Group On = Each Value
> MajorClassName (Ascending)
> Group Header = Yes
> Group Footer = Yes
> Group On = Each Value
> MinorClassName (Ascending)
> Group Header = Yes
> Group Footer = Yes
> Group On = Each Value
> IncidentID
> Group Header = No
> Group Footer = No
> Group On = Each Value
> SQL Statement (QBE)
> EmployeeName (GroupBy)
> MajorClass (GroupBy)
> MinorClass (GroupBy)
> IncidentID (Count) - from tblResponses!
> DispatchTime (GroupBy)
> InServiceTime (GroupBy)
> TimeDiff (GroupBy)
> TimeDiff:
IIf([InServiceTime]>>[DispatchTime],Format([InServiceTime]-[DispatchTime]),"S
> hort Time"),Format(DateAdd("d",1,[InServiceTime])-[DispatchTime]),"Short
> Time"))
> Sample Output:
> Brown, Joe
> Calls
> EMS
> 001 11:00 13:59 2:59
> 003 09:13 10:29 1:16
> Count = 2 Time = 4:15
> Fire
> 002 23:04 01:41 2:37
> 004 11:42 12:02 0:20
> 005 04:30 05:58 1:28
> Count = 3 Time = 4:25
> Total Count = 5 Total Time = 8:40
> Drills
> External
> 001 11:00 12:00 1:00
> 003 09:00 10:30 1:30
> Count = 2 Time = 2:30
> EMS
> 002 19:00 21:30 2:30
> 004 11:00 12:00 1:00
> 005 09:30 11:00 1:30
> Count = 3 Time = 5:00
> Total Count = 5 Total Time = 7:30
> {Next Employee ....}
> Counts and times are only totaled per employee; there is no aggregate grand
> total.