hours, minutes, seconds

[Karl]

I am new to Access.

I need a field in my database that records the duration of an event in
hours, minutes, and seconds. I will then need to determine the average of
these events

I tried using a "General Date" field with a "h:n:s" format. That seemed to
record the times okay but I couldn't get the report to compute the average.
When I click on "Totals" in "Grouping & Totals" in the report's design view
the only options I get are "Count Records" & "Count Values".

Any suggestions?

 

[Steve]

Use three number fields; one for hours, one for minutes and one for seconds.
Use a query as the recordsource of the report. Use calculated fields to
determine the average of the events.

Steve
(e-mail address removed)

 

[John W. Vinson]

I am new to Access.

I need a field in my database that records the duration of an event in
hours, minutes, and seconds. I will then need to determine the average of
these events

I tried using a "General Date" field with a "h:n:s" format. That seemed to
record the times okay but I couldn't get the report to compute the average.
When I click on "Totals" in "Grouping & Totals" in the report's design view
the only options I get are "Count Records" & "Count Values".

Any suggestions?

A Date/Time field in Access is best suited for storing a precise moment in
time; if you just have the time portion, it's actually a time on December 30,
1899 (the zero point for dates). That is, 6:00:00 is actually stored
internally as 0.25, and corresponds to #12/30/1899 06:00:00#.

An effect of this is that if you're storing durations, things get strange when
the duration (or the sum of the durations) goes over 24 hours: instead of
seeing 26:00:00 you'll see #12/31/1899 02:00:00# or just 2:00:00 if you
suppress the date part!

Your best bet is to store the duration in a Long Integer count of seconds; you
can use an expression like

[duration] \ 3600 & ":" & Format([duration] \ 60 MOD 60, "00:") &
Format([duration] \ 60, "00")

to split it out into hours, minutes and seconds; the number field will average
and total properly.

 

[Karl]

Thanks, I was afraid it would be something like that.

Having to enter hours, minutes and seconds in separate fields isn't optimal.
The whole idea behind this database is to move away from the unwieldy Excel
spreadsheet that we've been doing this work on. Excel does have a problem
with recording and averaging times... I can hear the users' whining already
(even if Access does many other things better than Excel for our purposes).

Some of these values may be over 24 hours, so I can't use Ken's solution.

I entered some test values and was able to convert hours and minutes into
seconds, add them all up and get the average; howeer, when I use John's
formula I get a three digit value for the seconds (hours and minutes were
fine). It is also off by about 11 seconds from the average of the same
values in Excel.

I am new to Access.

I need a field in my database that records the duration of an event in
hours, minutes, and seconds. I will then need to determine the average of
these events

I tried using a "General Date" field with a "h:n:s" format. That seemed to
record the times okay but I couldn't get the report to compute the average.
When I click on "Totals" in "Grouping & Totals" in the report's design view
the only options I get are "Count Records" & "Count Values".

Any suggestions?

A Date/Time field in Access is best suited for storing a precise moment in
time; if you just have the time portion, it's actually a time on December 30,
1899 (the zero point for dates). That is, 6:00:00 is actually stored
internally as 0.25, and corresponds to #12/30/1899 06:00:00#.

An effect of this is that if you're storing durations, things get strange when
the duration (or the sum of the durations) goes over 24 hours: instead of
seeing 26:00:00 you'll see #12/31/1899 02:00:00# or just 2:00:00 if you
suppress the date part!

Your best bet is to store the duration in a Long Integer count of seconds; you
can use an expression like

[duration] \ 3600 & ":" & Format([duration] \ 60 MOD 60, "00:") &
Format([duration] \ 60, "00")

to split it out into hours, minutes and seconds; the number field will average
and total properly.

 

[John W. Vinson]

Thanks, I was afraid it would be something like that.

Having to enter hours, minutes and seconds in separate fields isn't optimal.
The whole idea behind this database is to move away from the unwieldy Excel
spreadsheet that we've been doing this work on. Excel does have a problem
with recording and averaging times... I can hear the users' whining already
(even if Access does many other things better than Excel for our purposes).

Some of these values may be over 24 hours, so I can't use Ken's solution.

I entered some test values and was able to convert hours and minutes into
seconds, add them all up and get the average; howeer, when I use John's
formula I get a three digit value for the seconds (hours and minutes were
fine). It is also off by about 11 seconds from the average of the same
values in Excel.

Sorry... typo (or brainfade) on my part; the formula should be

[duration] \ 3600 & ":" & Format([duration] \ 60 MOD 60, "00:") &
Format([duration] MOD 60, "00")

with a MOD rather than an integer divide for the seconds.

You can make data entry a bit easier by having a Form with four textboxes:
three unbound, for hours, minutes and seconds, and the fourth bound to
Duration. In the afterupdate event of each of the unbound textboxes include
code like

Private Sub txtHrs_AfterUpdate()
Me!txtDuration = 3600*NZ(Me!txtHrs) + 60*NZ(Me!txtMin) + NZ(Me!txtSec)
End Sub

This can be made more sophisticated if you want - as written it will store
86400 in the Duration field if the user just types 24 in txtHrs and leaves the
other two blank. You might or might not want that!

You can also put code in the form's Current event to do the reverse:

Private Sub Form_Current()
If Not IsNull(Me!txtDuration) Then
Me!txtHrs = Me!txtDuration \ 3600
Me!txtMin = (Me!txtDuration \ 60) MOD 60
Me!txtSec = Me!txtDuration MOD 60
End If
End Sub