Ignore zeros when averaging

[DonHind]

I have a report that summarizes lab data. Occasionally they do not run all the tests and a field will have a zero in it. When I average on the report I would like to ignore the zeros. I tried to do IIF function in the summation field like Excel does on columns to ignor zeros, but the report would not run, said the report was too complicated. Can I do something to the query that feeds the report, perhaps convert zeros to null, since the average function ignors zeros?

 

[mp9]

Do the average calculation manually, i.e. SUM the column and divide it by a DCOUNT, which will let you count only those records that have a value greater than 0 for your field.

http://www22.brinkster.com/accessory/