Exclude Zero Values

[ggovensky]

Exclude Zero Values When You Calculate Averages
Hello All, When you use the Avg() function in a report to average a set of values, the function uses records that contain zero values in the calculation. However I do want "0" 's to show up on reports if the Totals are "0". (I used the nz fuction) For the report footer, the DAvg function does not works too! I am calculating a Total of 20 different controls and the TotalAverage is an unbound control.

Any ideas?

Very much appriciated!!

 

[Mile-O]

If you have a second query that excludes all the 0 values you can calculate the average of that in your unbound control anduse the query you have for the bound controls on your report.

 

[ggovensky]

Thanks for the quickness Mile-O-Phile. I tried what you said...but what I am looking for is not working. Please help. I've included my database. End results, I am looking for a Avg() for the Average on the Employee Form, and on all the reports that does not calculate any 0's or null values. Thanks a bunch.

George

 

[ggovensky]

For some reason I can not upload the database at 166kbs.