Prev: querying unrelated tables
Next: Maybe Update Query?
From: SSi308 on 4 Jun 2010 08:19
Thanks for the reply Ken.
If I add the function to the database will that affect all DateTime fields?
In addition to the LengthOfCall field there is a DateOfCall and TimeOfDay
field. Many of my reports are based on these two fields.
By adding the function will I be "breaking" anything?

Lori

"KenSheridan via AccessMonster.com" wrote:

> 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/Forums.aspx/access-queries/201006/1
>
> .
>
From: KenSheridan via AccessMonster.com on 4 Jun 2010 11:09
Lori:

Rest assured, adding the function to the database won't change any data at
all. All it does is reformat a value as hours;minutes:seconds when it's
called in a query or report. Calling it with the average of the LengthOfCall
fields in a query with TimeToString(Avg([LengthOfCall])) won't affect any of
the other columns or change any of the underlying values in the LengthOfCall
column, it will merely show the average as a time duration of hours;minutes:
seconds rather than as a decimal number of days, which is what you'd get if
you simply averaged the LengthOfCall values without calling the function.

BTW if you paste the function into a new module be sure to save the module
with a different name from that of the function; call it something like
basDateTimeStuff for instance, but the choice of a name for the module is
entirely yours.

Ken Sheridan
Stafford, England

SSi308 wrote:
>Thanks for the reply Ken.
>If I add the function to the database will that affect all DateTime fields?
>In addition to the LengthOfCall field there is a DateOfCall and TimeOfDay
>field. Many of my reports are based on these two fields.
>By adding the function will I be "breaking" anything?
>
>Lori
>
>> Lori:
>>
>[quoted text clipped - 95 lines]
>> >
>> >Lori

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/201006/1

First  |  Prev  | 
Pages: 1 2
Prev: querying unrelated tables
Next: Maybe Update Query?