Form calculations with errors

[dpelizzari]

I am currently working on a form that calculates the sum of counts and charges for various categories. The form sums the number of items in each category, then the sum of the charges. Where I am stuck is trying to break out the "per unit" charge for each category. The calculation works fine (sum of charges/sum of items) when there is a number greater than 0, but if the count or charges are 0, the fields display errors. I attempted to compensate for this by setting the text box value on the form to iif(iserror(sum of charges/sum of items),0,(sum of charges/sum of items), but it still shows the error!:banghead:. I could probably write nested iif statements to evaluate the values of each sum, but you would think this would be simpler. Help? Here is a sample of the actual ControlSource field on one of the text boxes: =IIf(IsError([SumOfOracle_Qual_Dev_Charge]/[SumOfBaseline Qualified Developer]),0,[SumOfOracle_Qual_Dev_Charge]/[SumOfBaseline Qualified Developer])

 

[dpelizzari]

Thanks, AccessBlaster, I was hoping it would be simpler, but, I guess changing the formula to this works:
=IIf(Nz([SumOfBaseline Qualified Developer]),IIf(Nz([SumOfOracle_Qual_Dev_Charge]),[SumOfOracle_Qual_Dev_Charge]/[SumOfBaseline Qualified Developer]),0) I just have to change 10 formulas and remember to do this for any new ones.

 

[dpelizzari]

Heh, actually since the calculation and logic was the same (only the field names changed), I pasted the field names into Excel and did something like ="=iif(nz("&a:a&"),iif(nz("&b:b&"),"&a:a&"/"&b:b... then I could just copy down the formula and paste it in.