Avoiding divide by zero error

[Crispy]

Hi all.

I have three subforms on my main form. They all display salary details from different sources, and each subform has 2 calculated fields in the form footer to show the average salary and the number of records on which the average has been calculated.

I then have a field on the main form to calculate the average of the averages which works OK unless one of the subforms has null or zero in it, in which case it returns no information.

Can anyone suggest a way of bypassing this divide by zero error? The code I'm using to calculate the average of averages is as follows:

Me!Text44 = ((Me![Frm1 subform].Form!text4 * Me![Frm1subform].Form!Text6) + (Me![Frm2 subform].Form!text4 * Me![Frm2 subform].Form!Text6) + (Me![Frm3 subform].Form!Text6 * Me![Frm3 subform].Form!Text8)) / (Me![Frm1 subform].Form!Text6 + Me![Frm2 subform].Form!Text6 + Me![Frm3 subform].Form!Text8)

Thanks for any help you can provide...

 

[Newman]

Just add that red text to your current text. (Don't forget the ending «)»

iif (Me![Frm1 subform].Form!Text6 + Me![Frm2 subform].Form!Text6 + Me![Frm3 subform].Form!Text8)=0,"N/A",Me!Text44 = ((Me![Frm1 subform].Form!text4 * Me![Frm1subform].Form!Text6) + (Me![Frm2 subform].Form!text4 * Me![Frm2 subform].Form!Text6) + (Me![Frm3 subform].Form!Text6 * Me![Frm3 subform].Form!Text8)) / (Me![Frm1 subform].Form!Text6 + Me![Frm2 subform].Form!Text6 + Me![Frm3 subform].Form!Text8))

 

[Crispy]

Thanks for your reply. Unfortunately, I didn't make myself clear on the original post. If one (or two) of the subform fields are zero, I want the calculation to ignore them and perform the calculation only on the fields with valid data. Any ideas on how to do this?

 

[Bat17]

You should only get Div by 0 error if all three are 0 otherwise there will be a value in calculation. Pesumably it is actualy null values that are causing the problem so you need to wrap every part in NZ() functions

NZ(Me![Frm1 subform].Form!Text6 ,0)

You will still need Newmans adjustment though to handle when all field are Null/0

HTH