If the value in the [Total Time Early or Late] column is a string expression
returned by the function, you won't be able to average it. As date/time data
type is implemented as a 64 bit floating point number, with the integer part
representing days and the fractional parts the times of day, you can simply
subtract one date/time value from the other and average the results. This
will give you the average time difference expressed as 2.1234 days for
instance. This can then be formatted using the following function:
Public Function FormatDuration(dblDuration As Double, Optional blnShowDays
As Boolean = True) As String
Const HOURSINDAY = 24
Dim lngHours As Long
Dim strMinutesSeconds As String
Dim strDaysHours As String
'get number of hours
lngHours = Int(dblDuration) * HOURSINDAY + _
Format(dblDuration, "h")
' get minutes
strMinutesSeconds = Format(dblDuration, "nn")
If blnShowDays Then
'get days and hours
strDaysHours = lngHours \ HOURSINDAY & _
" day" & IIf(lngHours \ HOURSINDAY <> 1, "s, ", ", ") & _
lngHours Mod HOURSINDAY
FormatDuration = strDaysHours & " hours, " & _
strMinutesSeconds & " minutes"
Else
FormatDuration = lngHours & " hours, " & _
strMinutesSeconds & " minutes"
End If
End Function
So the expression used as the ControlSource of a text box in a group footer
or report footer to return the average time late would be along these lines:
=FormatDuration(Nz(Avg(([DateTimeArrived]-[DateTimeDue])*IIf([DateTimeArrived]>[DateTimeDue],1,Null)),0))
This works by subtracting the time due from the time arrived, then
multiplying this by the return value of the IIf function which returns 1 id
the time arrived is later than the time due (i.e. late) or Null otherwise.
As multiplying any value by Null results in Null the on-time rows are not
taken account of in the averaging which is then applied by the Avg operator,
so only the late rows are averaged. The Nz function is applied to the result
to return an average of zero rather than Null if no rows are late. Finally
the resulting value is formatted with the FormatDuration function.
This does not of course give you the alternative value of "Item is on time".
Asking it to return this doesn't make any sense in fact when averaging a set
of values, as it refers to a single item not the set. If you did want an
alternative to the average length of time over the set this would only be
appropriate if no rows in the set were late, i.e if the above expression
returned a zero average duration.
You might have noticed that the function includes an optional blnShowDays
argument, defaulting to True. If false is passed into the function as the
second argument the return value would be the total number of hours and
minutes rather than days hours and minutes. The expression for this would be:
=FormatDuration(Nz(Avg(([DateTimeArrived]-[DateTimeDue])*IIf([DateTimeArrived]>[DateTimeDue],1,Null)),0),False)
Ken Sheridan
Stafford, England
Matt said:
I am developing a tracking database that will deliver a report which will
tell if a document is early or late. I would like to calculate the Average
time for those items that are marked "Late".
I used an IIf statement in a text box on my Report which reads:
IIf([Early or Late]="Late", Avg([Total Time Early or Late]), "Item is on
time")
[Total Time Early or Late] is a calculated field that returns the difference
in two General Dates in the following format: "XXdays XXhrs XXmins" using the
code on this web page: (
http://www.accessmvp.com/djsteele/Diff2Dates.html)
Right now the answer is returned in a general number format (i.e. 2.818)
I would like the answer of my IIf statement to be formatted in the same
manner as the diff2dates function:
Average Days Late: "2 days, 4 hrs, 13 mins"
Any idea how I would accomplish this?
Thanks
