Lori:

While the DateTime data type in Access stores a point in time rather than a
duration, it can be used for the latter provided that each duration is less
than 24 hours. This is due to the fact that date/time values in Access are
implemented as a 64 bit floating point number, with the integer part
representing days and the fractional part the times of day. Access starts
counting from 31 December 1899 00:00:00, which is implemented as zero.

If you do maths on date/time values you get some strange results if you
format the result as date/time because what you are really seeing is the
length of time after 31 December 1899 00:00:00. If the result is formatted
as a number, however, what you see is the length of time in days. To
illustrate this 2.123 days if formatted as date/time is:

1 January 1900 02:57:07

If you need to store durations of more than 24 hours you have little choice
but to store them as numbers, or better still as separate fields for days,
hours, minutes and seconds (with DefaultValue properties of zero for each) as
this makes data entry intuitive. The four values can then be processed into
a single value of seconds and mathematical operations done on that value,
then converting the result back into its four constituents units for output.

However, for durations of less than 24 hours, which I'd guess your calls will
be, you can quite happily use a date/time data type. When you enter the
duration what you are really entering is a date/time on 31 December 1899, but
that is not relevant as the underlying implementation is a number of zero
point something, and it's this number on which arithmetical operations will
be undertaken under the skin. So you can average a set of date/time values
where each is less than 24 hours and the result will again be a number of
zero point something. It's very easy to display this as a time by adding the
following function to the database:

Public Function TimeToString(dtmTime As Date, _
Optional blnShowdays As Boolean = False) As String

Dim lngDays As Long
Dim strDays As String
Dim strHours As String

' get whole days
lngDays = Int(dtmTime)
strDays = CStr(lngDays)
' get hours
strHours = Format(dtmTime, "hh")

If blnShowdays Then
TimeToString = lngDays & ":" & strHours & Format(dtmTime, ":nn:ss")
Else
TimeToString = Format((Val(strDays) * 24) + Val(strHours), "00") & _
Format(dtmTime, ":nn:ss")
End If

End Function

So in a query for instance you could have a computed column:

AverageCall: TimeToString(Avg([CallDuration]))

or you can do similarly in a computed control in a footer in a report:

=TimeToString(Avg([CallDuration]))

You'll have noticed that the function includes an optional blnShowdays
argument. If you omit this any result of 24 hours or more will show as hours:
minutes:seconds. But if you pass True into the function as this argument it
will return days:hours:minutes:seconds. This might be done when summing the
times over a long period to get the total calls duration, e.g.

=TimeToString(Sum([CallDuration], True))

Mostly you'd omit the optional argument and return the result in hours:
minutes:seconds, e.g.

53:36:58

but you do have the option to return it as:

2:05:36:58

if you wish.

Ken Sheridan
Stafford, England

SSi308 wrote:
I have a call database that includes a field for length of call. I have
created a query that includes employee, date, and length of call.

I need to calculate the average call length per employee and as a whole. I
found one post that suggested the following:
"To store time intervals you should store them as integers that represent
the number of hours or minutes or seconds, etc.. and then you can perform
whatever math you like. The results can then be converted to the hh:nn:ss
format for display. "

How is this accomplished?

Lori

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/201006/1