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.

 

[Pat Hartman]

I find it easier (and more accurate) to calculate things like averages if I use null as the default for my numeric fields. There is a difference between 0 as a known value which should be included in all calculations and 0 as an unknown value which should not be included. And that is why I use null as my default value. Otherwise, there is no way to determine which 0's are known values verses those that are unknown values and just defaulted to 0.

Access aggregate functions, such as Avg(), ignore null values so if you have three values, 4, null, 2 - the average would be 3 NOT 2. Whereas the average of 4,0,2 would be 2.