Field Formatting

[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

 

[Allen Browne]

Use the Format() function to display the 2 digits.
Use the backslash character in the format string to specify a literal.

This example assumes the total count is available in seconds:

=Format([Seconds] \ 3600) & Format(([Seconds] Mod 3600) \ 60, "\:00") &
Format(([Seconds] Mod 3600) Mod 60, "\:00")

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

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

 

[Thomas M]

Allen,

Thanks for the reply. I was on the right track, but didn't know about
using the backslash to specify a literal. I've got it working now.

I posted a similar question about 2 months back, and it turns out that
you replied with much the same information as you gave me this time
around. However, at that time I wasn't far enough along to completely
understand your reply. I ended up putting the issue aside for a couple
of months, and when I came back to it last week I was able to view
things from a fresh perspective and get past the problems that had been
plaguing me up to that point. Having solved most of the problems, I've
now reached a point where I can understand the information that you gave
me back then.

Thanks for your help!

--Tom

Subject: Re: Field Formatting
From: "Allen Browne" <[email protected]>
Newsgroups: microsoft.public.access.reports

Use the Format() function to display the 2 digits.
Use the backslash character in the format string to specify a literal.

This example assumes the total count is available in seconds:

=Format([Seconds] \ 3600) & Format(([Seconds] Mod 3600) \ 60, "\:00") &
Format(([Seconds] Mod 3600) Mod 60, "\:00")

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

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)

 

[Allen Browne]

That's really good news, Tom.

Sounds like you are progressing well in understanding how Access works.

All the best

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Allen,

Thanks for the reply. I was on the right track, but didn't know about
using the backslash to specify a literal. I've got it working now.

I posted a similar question about 2 months back, and it turns out that
you replied with much the same information as you gave me this time
around. However, at that time I wasn't far enough along to completely
understand your reply. I ended up putting the issue aside for a couple
of months, and when I came back to it last week I was able to view
things from a fresh perspective and get past the problems that had been
plaguing me up to that point. Having solved most of the problems, I've
now reached a point where I can understand the information that you gave
me back then.

Thanks for your help!

--Tom

Subject: Re: Field Formatting
From: "Allen Browne" <[email protected]>
Newsgroups: microsoft.public.access.reports

Use the Format() function to display the 2 digits.
Use the backslash character in the format string to specify a literal.

This example assumes the total count is available in seconds:

=Format([Seconds] \ 3600) & Format(([Seconds] Mod 3600) \ 60, "\:00") &
Format(([Seconds] Mod 3600) Mod 60, "\:00")

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)