Problem with Report Grand Total

[Thomas M]

Access 2003

I have a report that breaks down my time usage by category, and shows
the date, start time, end time, duration, and description of each task
that I have performed. This report is based on a query. The duration
of each task is not stored in the database--it is calculated by the
query.

Here are the Duration sub-totals for each category, along with the grand
total for the report (all times are in hh:mm:ss format):

Category Total Time
E-Mail 3:49:52
General 5:19:54
Personal 0:44:41
PR 2:38:27
Programming 3:42:31
Reports (General) 11:07:24
Reports (Running) 8:01:50
Trouble Tickets 0:02:17
System Admin 5:20:52
Tech Support 8:32:30

Grand Total 1:20:16

By my calculations, the grand total should come out to be 49:20:16.
All the category totals are correct. The formula for the Grand Total
is:

=DSum("[Time Usage Query]![Duration]","Time Usage Query")

It looks like the DSum function starts over after each 24 hours. This
would explain why the grand total comes out as 1:20:16.

49:20:16 - 48:00:00 = 1:20:16

The question is: How do I fix it?

--Tom

 

[Allen Browne]

Internally, Access stores date/time values as a floating point number, where
the whole number represents the date, and the fraction is a fraction of a
day (e.g. noon = 0.5.) When you sum date/time values, the overflow, it
therefore reports the time on a different day. If you format the Grand Total
text box as "General Date", it will probably report 1:20pm on Jan 3 1900.

For more info, see:
Calculating elapsed time
at:
http://allenbrowne.com/casu-13.html