Cannot Average Time Difference 2

[timoteo]

I am trying to use Access to generate a report that will list the average time difference between two events by month.

At first I tried using a calculated field in my query then group by month in the report and use an unbound control and the Avg() function in the group footer. My formula in the calculated field looked like this:

Mutual Aid Response Time: Format(tblMutualAid.FirstDispatchTime-1-tblMutualAid.OnSceneTime,"hh:nn:ss")

I had to use the -1 to account for those instances where the two times spanned midnight. Everything work up until this point. The problems started when I tried to average the results by month. I used =Avg([Mutual Aid Response Time]) in a text box in the group footer section. When I previewed the report I got a data type mismatch error.

I assumed that this was caused by using the Format function for the calculated field in the query. So I tried a different tactic. I removed the format function and use this for the calculated field:

Mutual Aid Response Time: tblMutualAid.FirstDispatchTime-1-tblMutualAid.OnSceneTime

Then I applied Avg and format functions to the results. This method took care of the type mismatch error, but it does not report the correct result if one of the records for that month has values that span midnight.

The time fields I am working with have not day information associated with them. By this I mean that when I use the long date format, all the record have the same day and year (1899 IIRC).

Does anyone know of a way around either of these issues?

Thank you in advance.


Timoteo

 

[SkipVought]

Hi,

Might be helpful if you posted the SQL you referenced.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[dhookom]

I never format in a query unless the records are being exported. I would try:

Mutual Aid Response Time:Val(IIf(OnSceneTime < FirstDispatchTime, 1 - FirstDispatchTime +  OnSceneTime ,OnSceneTime-FirstDispatchTime))

Duane
Hook'D on Access
MS Access MVP

 

[timoteo]

SkipVought,

Here is he code you requested:

SELECT tblMutualAid.DispatchDate, tblMutualAid.FirstDispatchTime, tblMutualAid.CallNum, tblMutualAid.Unit, tblMutualAid.OnSceneTime, tblMutualAid.FirstDispatchTime-1-tblMutualAid.OnSceneTime AS [Mutual Aid Response Time]
FROM tblMutualAid
WHERE (((tblMutualAid.Unit) Like "M*" Or (tblMutualAid.Unit) Like "A*"));

dhookom,

Thanks for tip. I will try it and let you know how it goes.

Timoteo

 

[SkipVought]

Mutual Aid Response Time: [b]Format[/b](tblMutualAid.FirstDispatchTime-1-tblMutualAid.OnSceneTime,"hh:nn:ss")

The Format function returns TEXT not "TIME".

You cannot do arithmetic with TEXT.

TIME is in units of days. Use the DateDiff function or manipulate days.


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[timoteo]

SkipVought,

Thanks for the advice on DateDiff function. It was the first thing I tried and it did not work for two reasons. First, it does not return the correct time if the two times span midnight and second it will not allow me to format the time the way I need it. DateDiff will only return the number of days, hours, minutes, or seconds rather than the hh:nn:ss format.

dhookom,

I tried your calculated field and it returns 12 for all records that span midnight. Your formula in the IIF function for the truepart appears correct. I exported the query results into Excel and applied your formula and it yielded the correct results.

Thank you both for your assistance. Any other suggestions?

Timoteo

 

[SkipVought]

What you need then is an IIF statement, and I'm using prose rather than the actual fields...

IIF([The Difference]<0,-1,0)

Add this to your formula, like...

tblMutualAid.FirstDispatchTime+IIF([The Difference]<0,-1,0)-tblMutualAid.OnSceneTime AS [Mutual Aid Response Time]

Now your difference will be in units of days .

Please keep in mind, however, that Date & Time are POINTS IN TIME, and differences between two points in time is a DURATION. This distinction should be kept in mind, as it can become significant when the difference is greater than one day.

As a matter of practice, ALL TIME VALUES should have a DATE VALUE along with it. Doing so would negate the need for the IIF.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[timoteo]

SkipVought,

I was able to get it to work using your code. Thank you.

SELECT tblMutualAid.DispatchDate, tblMutualAid.FirstDispatchTime, tblMutualAid.CallNum, tblMutualAid.Unit, tblMutualAid.OnSceneTime, [FirstDispatchTime]+IIf([FirstDispatchTime]-[OnSceneTime]>0,-1,0)-[OnSceneTime] AS [Mutual Aid Response Time]
FROM tblMutualAid
WHERE (((tblMutualAid.Unit) Like "M*" Or (tblMutualAid.Unit) Like "A*"));

You may notice that I had to change the "less than" symbol to a "greater than" to get it to work.

Thanks to you and dhookom for introducing me to the IIF funtion.