Hale, Jim
jim.hale at fleetpride.com
Fri Nov 14 14:11:19 CST 2003
Gustav-
If you ever decide to put all your code solutions into a book ("Gustav's
Goodies"?) I'll be first in line to buy it. Thanks for a ton of useful code
over the years.
Jim Hale
-----Original Message-----
From: Gustav Brock [mailto:gustav at cactus.dk]
Sent: Friday, November 14, 2003 11:52 AM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Problem: subtotaling time (long email)
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.
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com