Averaging a column some have zero data

[Bobby1st]

Hello,
I have an existing table with data and I added a new column with a default to zero. When I query the table-column the results will show zero content for the previous dates and data for the current dates. I have a " =Avg([theColumn]) " on a textbox. The result in not correct, it counts the zero rows as part of the divisor. Any idea how to write the code?

Thanks

 

[JoeyY]

Have you tried using DAvg?

=DAvg("[theColumn]","tableorqueryname","[theColumn]>0")

 

[Bobby1st]

JoeyY,

I WORKS!, thank you. I have seen it but didn't have chance to apply to a situation, similar to seldom use SumIf. Wonderful!

Thanks,
Bobby1st

 

[Bobby1st]

JoeyY,

I thought it was working right but it was brought to my attention that, this function is giving blank result if there no data instead of zero. *** I solved this one: =iif(Avg([Flexible]=0,0,DAvg([Flexible],"qryYTD Rollup",[Flexible]>0)) ***

Second, my test report have 10 line item months, six zeroes for 2007 and 4 months with data (Jan-Apr 2008). The DAvg() is using divisor of 10 instead of 4.

For trend, the report details monthly data. At the bottom is a textbox where the y-t-d totals appears, mostly avg(). I can't use in the divisor the months the data is not yet collected.

Can you spot were I am getting it wrong with you expert eyes.

Thank you.

 

[Rich]

Just put >0 in the query criteria column for the field in question

 

[Pat Hartman]

Leave your query alone and fix the data.
1. Remove the default from the table so that new rows don't start out with a 0 value.
2. Run an update query that updates all the 0 values to null.

Now, 0 will be meaningful and will be included in the average when it is supposed to be. If no data is entered, the value will be null and aggregate functions will ignore it so the average will be correct.

Avg - 3,0,3 = 2
Avg - 3, null, 3 = 3