Help Divide by zero error

[CCIDBMNG]

I'm running a complex query and I'm trying to get an average of a field for a 2 week period. The only problem is sometimes there will be zeros and I receive an error because of the division by zero. I don't know how to get around this. The calculation is Sum([Pledge Amount]*[2WSum])/Sum([2wsum]). I've tried using IIf([2wSum]=0,0,Sum([Pledge Amount]*[2WSum])/Sum([2wsum])) but I receive the error you tried to evaluate a query that does not include the specified expression as part of an aggregate function. Does anyone know a way around this?

 

[barbarossaii]

Hi CCIDBMNG,

regarding the error message: "You tried to evaluate a query that does not include the specified expression as part of an aggregate function":

got this too; both of the following solutions could help.

1.) In a query where I used "group" in the design view I had to switch to the filed w/ the formula to "expression" or "formula" (don't know exact engl. wording bec. using non-Engl. version).

2.) When I tried to break a complex query in several smaller ones and basing one on the others, the problem vanished. (in ur example sum(s) in 1st query, average in 2nd query based on 1st query)

HTH,
Barbarossa II