Allow 0 in Avg expression

[ladybug via AccessMonster.com]

I have this in query field Average: Avg(tblProductionDetail!
intNew_Scripts/tblProductionDetail!intScript_Orders)

This worked fine before, but the data now is putting "0" in these two fields
instead of leaving them blank as it did before.

How can I update this expression to allow for "0"?

 

[KARL DEWEY]

This worked fine before, but the data now is putting "0" in these two fields
What two fields? You only have one field as output in your posting.

It seems unusual to perfom average on division of two fields - substraction,
addition or multiplication yes but not division.

What is the rest of your query? Post the SQL by opening it in design view,
clicking on VIEW - SQL View, highlighting all, copying, and pasting in a post.

 

[John Spencer]

If tblProductionDetail!intScript_Orders is zero, do you want to include the
record in the average calculation or not. If not, then you could use the
following.

Avg(IIF(tblProductionDetail!intScript_Orders=0,Null,tblProductionDetail!intNew_Scripts/tblProductionDetail!intScript_Orders))

If you want the record included in the calculation then you would need to
replace null with a zero(0) or some other number value that you want to use.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County