Averaging 'Short Time' formatted criteria in a footer

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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!
 
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!
 
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>
 
Back
Top