Totalling Times in a Report

S

Steve Maca

I am collecting times people spend on specific activities and then wanting to
report on the time spent on a weekly and monthly basis in a summary format.

The report needs to show the total time spent by each person, on each
activity.
In the report is grouped by the peoples names and in the group footer is the
control with the Source =Sum([Social_Behavioural])
A total time is required of all time spent on an activity. So in the report
footer is a control with the came Source of =Sum([Social_Behavioural]).

The time is recorded in a Short Date format (hh:nn). The final report can
not use decimal time.

My problem is the short date format only shows time up to 24:00. Within a
given reporting period times >24 hour may be recorded against and activity
but the report will not show it. For example:
A sum of 36 hours 20 minutes is reported as 12:20 instead of 36:20

Help would be appreciated,

Steve.
 
A

Allen Browne

The Access date/time data type is stored internally as a fractional number,
where the whole number represents the date and the fraction represents the
time of day (0.5 = noon, 0.26 = 6am, etc). If you sum times, it therfore
wraps to the next day, and your expression - formatted to show the time
only - gets just the left over time.

The best solution is to store the time as a whole number of minutes (or
seconds or whatever), and you can then format it to show the total. This
article explains how:
Calculating elapsed time
at:
http://members.iinet.net.au/~allenbrowne/casu-13.html

You can use the same technique by creating a text box to calculate the
number of minutes on your report. Give it these properties:
Control Source: =DateDiff("n", #0:00:00#, Sum([Social_Behavioural]))
Name: txtMinutes
Format: General Number
Visible: No

Now set the Control Source of your visible text box to show that value as
hours and minutes:
=[txtMinutes] \ 60 & Format([txtMinutes] Mod 60, "\:00")
 
Top