hours, minutes, seconds
in [Getting Started]
|
Prev: Open mode
Next: Forms Field List
From: Karl on 17 Feb 2010 17:15 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?
From: Steve on 17 Feb 2010 17:26 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 santus(a)penn.com "Karl" <Karl(a)discussions.microsoft.com> wrote in message news:C8F4AB84-3E9D-45E0-8E5F-B8A56969C4B3(a)microsoft.com... >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?
From: John W. Vinson on 17 Feb 2010 17:47 On Wed, 17 Feb 2010 14:15:01 -0800, Karl <Karl(a)discussions.microsoft.com> 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]
From: KenSheridan via AccessMonster.com on 17 Feb 2010 18:56 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
From: Karl on 18 Feb 2010 16:49
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 <Karl(a)discussions.microsoft.com> > 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] > . > |