hours, minutes, seconds

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?

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



"Karl" wrote in message
...
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?

On Wed, 17 Feb 2010 14:15:01 -0800, Karl
wrote:

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 [MVP]

While the date/time data type is really intended to store a point in time, it
is possible to use it for time durations provided that each duration is less
than 24 hours. Moreover it is then possible to torture it into confessing
aggregate values from a set of durations. The reason this is possible is due
to the way Access implements date/time values as a 64 bit floating point
number as an offset from 30 December 1899 00:00:00 with the integer part
representing the days and the fractional part the times of day. So entering
the time #06:00:00# is really entering 6.00 AM on 30 December 1899, and this
is implemented as 0.25.

Now, if you sum 5 durations of 6 hours, what you'll get is an underlying
number of 1.25, which as a date/time value is 31 December 1899 06:00:00, so
this is not going to make a lot of sense as a total duration. The way you
torture it into confessing is by adding the following function to a standard
module in your database:

Public Function TimeElapsed(dblTotalTime As Double, _
Optional blnShowDays As Boolean = False) As String

Const HOURSINDAY = 24
Dim lngDays As Long
Dim lngHours As Long
Dim strMinutesSeconds As String

' get number of days
lngDays = Int(dblTotalTime)

' get minutes and seconds
strMinutesSeconds = Format(dblTotalTime, ":nn:ss")

'get number of hours
lngHours = Int(dblTotalTime) * HOURSINDAY + _
Format(dblTotalTime, "h")
' return total elapsed time either as total hours etc
' or as days:hours etc
If blnShowDays Then
lngHours = lngHours - (lngDays * HOURSINDAY)
TimeElapsed = lngDays & ":" & Format(lngHours, "00") &
strMinutesSeconds
Else
TimeElapsed = Format(lngHours, "#0") & strMinutesSeconds
End If

End Function

You can see test for yourself by entering the following in the debug window:

? TimeElapsed(#31 December 1899 06:00:00#)

or:

? TimeElapsed(#06:00:00# * 5)

each of which returns:

30:00:00

If you enter:

? TimeElapsed(#06:00:00# * 5, True)

i.e. specifying True as the optional blnShowDays argument you get:

1:06:00:00

In a report to average the durations you'd call the function in an expression
as the ControlSource of a text box in a group or report footer, e.g.

=TimeElapsed(Avg([DurationField]))

You can do the same in a query as a computed column:

TimeElapsed(Avg([DurationField])) AS AverageDuration

Note that the function returns a string, not a date/time value, so you can't
do date/time arithmetic on the returned value. Provided that the returned
value is less than 24 hours, however it can be turned into a true date/time
value with the CDate function provided that you do not show days:

CDate(TimeElapsed(Avg([DurationField])))

on which date/time arithmetic can then be performed.

If the individual durations can be of 24 hours or more, however, you will
have to use one of the methods suggested by John or Steve.

Ken Sheridan
Stafford, England

Karl wrote:
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?

--
Message posted via http://www.accessmonster.com

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.

"John W. Vinson" wrote:

On Wed, 17 Feb 2010 14:15:01 -0800, Karl
wrote:

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 [MVP]
.

On Thu, 18 Feb 2010 13:49:01 -0800, Karl
wrote:

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
--

John W. Vinson [MVP]