MS Aceess - Average of all records greater than zero in a field

[Sarma]

I have a field in an MS Acess query that has records like - 1,000; 534; 769.13; 0; 654; 0, etc. It can also have few null records.

I want to calculate the average of all records which are greater than zero. Ignore zero values or null values.

Appreciate your help.

 

[Brianwarnock]

Something like

SumofField/sum(iif(nz(field,0)>0,1,0))

Brian

 

[Sarma]

Thanks, Brian. But the expression did not work. Is there any syntax error ?

 

[pr2-eugin]

Sarma, could you show us the query you ended up with?

 

[Brianwarnock]

I hope you did not just copy what I wrote , I don't know your field names and said "something like"

For the future note that it is not very useful to merely say something did not work, did it flag a syntax error? Did it produce the wrong result?
And as Paul asked, show your code.

Brian