Need to average time difference calculations

[thatlem]

I am having trouble calculating an average, both in reports and in a query, for a series of calculated time differences. Basically this calculation takes into account times spread across midnight in a 24 hour clock. Therefore if Time 1 was at 23:50 and Time 2 was at 00:30, I'd get 40 minutes.

Time Difference: IIf(((([time2]-[time1])*1440)<0),((([time2]-[time1])*1440)+1440),(([time2]-[time1])*1440))

What I want to do is average all the individual time differences.

However, when I try to define an average in reports, the average is greyed out. And when I try to run it in a query, I get an error, indicating that the calculation is to complicated. I have even tried DAvg, DCount, and DSum, but they don't work either.

I'm also restricted in that the fields were originally set up as only a time field and not date/time, therefore I have no date to fall back on.

I'd welcome any suggestions, even if I had to redo the calculations to something more desireable to get the Avg to work.

Thanks in advance..

 

[thatlem]

Wouldn't you know that as soon as I'd post a request I'd figure it out. I used a DateDiff funtion within the initial Calculation before trying to run the average.

So I used...

TimeDiff: IIf([Time2]-[Time1]<0, (DateDiff("n",[Time1], [Time2],)+1440), DateDiff("n", [Time1],[Time2]))

Then used... Avg: Avg([Initial Query]![TimeDiff])