Using IIF to divide even when the denominator is 0.

[dickpierce]

I'm an Access newbie. I have Access 97 and I'm trying to calculate batting averages, using a query field. Unfortunately, some of the denominators in my data table are either blank or have a zero. I would like a formula that would let me divide Hits by At Bats without getting an error message. My criteria field is set to have a greater than or equal to .300 value. Can anyone help?

 

[Norie]

How about this expression?

BattingAverage:Iif([AtBats]<>0, [Hits]/[AtBats], 0)

Change the field names as necessary.

 

[dickpierce]

Thanks for replying, but nope your formula didn't work. Also, I neglected to mention that the sum of Hits divides the sum of At Bats.

 

[Norie]

When you say the formula didn't work what exactly do you mean?

Did it cause errors or not return the expected result?

In your original post (OP) you said:

divide Hits by At Bats

Perhaps this will work:

BattingAverage:Iif([Hits]<>0, [AtBats]/[Hits], 0)

 

[dickpierce]

Aha! It worked! Thanl you! Now I have another problem. I would like to add the sum 6 columns in which 4 of the columns contain no data. I know that I must use IsNull, but I can't seem to get it to work. Thanks

 

[dickpierce]

Norie! Your first formula worked, however, my fat fingers mistyped.