Averaging 'Short Time' formatted criteria in a footer

T

Trevor

I am having problems averaging a series of 'short time' formatted criteria in a group footer. the query the report runs from has the following field...
Format(IIf([AdHocReserved]="Reserved" And [ATA] Is Not Null,[ATA]-1-[Time Met],[Time Requested]-1-[Time Met]),"Short Time") AS ResponseTim

These 'ResponseTime's are displayed in a detail section of a report. I want to average them in a footer but keep getting the error message "This expression is typed incorrectly, or it is too complex to be evaluated..."

I have tried eliminating the formatting in the query and doing it in the report. This worked for a normal 24 hr clock but now when I have values that cross midnight (for instance Time Requested = 23:58 and Time Met = 00:04), I get messed up values for averages. I've tried many scenarios already to no avail... any ideas would be greatly appreciated!
 
D

Duane Hookom

I don't ever format the values in query. Apply the format where you want to
see the format (control of form or report). The only reason I have formatted
a column in a query is when the query was being exported or used in a Mail
Merge. Keep your columns numeric so that you sum, average,... in your
report.

--
Duane Hookom
MS Access MVP


Trevor said:
I am having problems averaging a series of 'short time' formatted criteria
Click to expand...
in a group footer. the query the report runs from has the following field...
Format(IIf([AdHocReserved]="Reserved" And [ATA] Is Not Null,[ATA]-1-[Time
Click to expand...
Met],[Time Requested]-1-[Time Met]),"Short Time") AS ResponseTime
These 'ResponseTime's are displayed in a detail section of a report. I
Click to expand...
want to average them in a footer but keep getting the error message "This
expression is typed incorrectly, or it is too complex to be evaluated..."
I have tried eliminating the formatting in the query and doing it in the
Click to expand...
report. This worked for a normal 24 hr clock but now when I have values that
cross midnight (for instance Time Requested = 23:58 and Time Met = 00:04), I
get messed up values for averages. I've tried many scenarios already to no
avail... any ideas would be greatly appreciated!
 
J

Jeff Boyce

Trevor

To add to Duane's comments, I'll point out that MS Access has a Date/Time
data type -- this is used to store point-in-time, not duration. If you are
trying to "average" something that you've formatted to display as a "short
time", you may be trying to average date/time values! What it shows
(display), and what it stores (date/time) are not the same.

Good luck

Jeff Boyce
<Access MVP>
 
Top