replace #Num! with 0

[chriswrcg]

every so often I will be faced with the following calculation in access. 0/452. (zero divided by some other number)
inevitably the end result in access is the #Num! result instead of just displaying 0.



What can I do to prevent this?

[JoeM]

You wouldn't get the NUM error by dividing 0 by some number. 0 divided by any number returns 0, and Access has no problems with that.
It has a problem going the other way, if you try to divide some number by 0 (for example, 452/0), as that is an illegal mathematical operation (not uniquely an Access thing).

The way around that is to first check to see if the field you are dividing by is zero first.
For example, if you were dividing Field1 by Field2, the modified calculation should look like:

IIF([Field2]=0,0,[Field1]/[Field2])

[chriswrcg]

Thanks so much. I didn't think the placement of the 0 made that much of an impact.

[JoeM]

You are welcome.

0 can never be in the divisor. It is a mathematical rule, as division by 0 makes no mathematical sense.

Here are some good write-ups/explanations on it.
http://mathforum.org/dr.math/faq/faq.divideby0.html
The "Elementary Arithmetic" section here: https://en.wikipedia.org/wiki/Division_by_zero