T
Thomas M
Access 2003
This is a pretty minor issue, and I've found a work-around that I can
live with, but if possible I would like to solve this small problem in a
manner that is a little more sophisticated.
I have a simple database that I've built for tracking my time usage at
work. It uses a form to capture the start time and end time for each
task, and then it uses that information to calculate the duration of
each task.
I've also built a report that shows my time usage by category for each
project, and the time usage grand total at the end of the report.
Initially, I did all this using date/time fields and functions.
However, I discovered a problem with that method. When the aggregate
time for a given section of the report exceeds 24 hours, the summing of
the date/time fields reset to zero. So, for example, if the aggregate
time usage comes to 24 hours and 8 minutes, the report total will show
only 8 minutes. I've been told that this is by design and is not a bug.
To overcome this problem, I created 3 new columns in the query that
produces the report called Hours, Minutes, and Seconds. As you might
imagine, these columns split off the hours, minutes, and seconds from
the Duration figure for each task. Then I just sum those columns on the
report. This gets around the 24 hour limitation that you run into when
summing date/time fields.
The report displays the total time usage using the following formula:
=Sum([Hours])+Int(Sum([Minutes])/60) & ":" & (Sum([Minutes]) Mod 60)
+Int(Sum([Seconds])/60) & ":" & (Sum([Seconds]) Mod 60)
This method has two drawbacks. First, say that the total number of
minutes comes out to 4. Instead of the total time usage displaying as
1:04:36, it will display as 1:4:36. Second, say that the total hours is
zero. Then the total time usage would display as :4:36. What I would
like to do is make it always display the total time in hh:mm:ss format.
The obvious problem here is that the sum from each of 3 columns and
produces a text string, so it can't be formatted using a date/time
format.
Is there a way to make the formatting specifications part of the formula
so that "1:4:36" comes out as "01:04:36" and ":4:36" comes out as
"00:04:36"?
BTW, my work-around is to display the hours, minutes, and seconds in
separate fields that are separated by fields containing ":". That
works, but it doesn't look as professional as what I would like.
--Tom
This is a pretty minor issue, and I've found a work-around that I can
live with, but if possible I would like to solve this small problem in a
manner that is a little more sophisticated.
I have a simple database that I've built for tracking my time usage at
work. It uses a form to capture the start time and end time for each
task, and then it uses that information to calculate the duration of
each task.
I've also built a report that shows my time usage by category for each
project, and the time usage grand total at the end of the report.
Initially, I did all this using date/time fields and functions.
However, I discovered a problem with that method. When the aggregate
time for a given section of the report exceeds 24 hours, the summing of
the date/time fields reset to zero. So, for example, if the aggregate
time usage comes to 24 hours and 8 minutes, the report total will show
only 8 minutes. I've been told that this is by design and is not a bug.
To overcome this problem, I created 3 new columns in the query that
produces the report called Hours, Minutes, and Seconds. As you might
imagine, these columns split off the hours, minutes, and seconds from
the Duration figure for each task. Then I just sum those columns on the
report. This gets around the 24 hour limitation that you run into when
summing date/time fields.
The report displays the total time usage using the following formula:
=Sum([Hours])+Int(Sum([Minutes])/60) & ":" & (Sum([Minutes]) Mod 60)
+Int(Sum([Seconds])/60) & ":" & (Sum([Seconds]) Mod 60)
This method has two drawbacks. First, say that the total number of
minutes comes out to 4. Instead of the total time usage displaying as
1:04:36, it will display as 1:4:36. Second, say that the total hours is
zero. Then the total time usage would display as :4:36. What I would
like to do is make it always display the total time in hh:mm:ss format.
The obvious problem here is that the sum from each of 3 columns and
produces a text string, so it can't be formatted using a date/time
format.
Is there a way to make the formatting specifications part of the formula
so that "1:4:36" comes out as "01:04:36" and ":4:36" comes out as
"00:04:36"?
BTW, my work-around is to display the hours, minutes, and seconds in
separate fields that are separated by fields containing ":". That
works, but it doesn't look as professional as what I would like.
--Tom