Fix expression to avoid div/0?? (1 Viewer)

I have this query expression that gives me a #NUM! error due to div/0.

PercentPaybackToDate: FormatPercent(((Sum(IIf([TransactionTypeID]=5,Nz([Amount]),0)))/(Sum(IIf([TransactionTypeID]=9,Nz([Amount]),0)))),0)

I can't for the life of me figure out how to test for a zero value in [Amount] to spit out a null or a zero if it = 0.
:banghead::banghead::banghead:

The way you test for a zero value is with an If:

If ([Amount]=0, 0, {Amount to show if not 0 here})

With that said, I do not for the life of me want to spend time figuring out where to put it in your code. When you get that convoluted in your expressions, its time to move it to a function inside a module and then pass it the data it needs to do the logic.

I actually add a function to all my databases to do division and have it automatically catch errors in the inputs:


That way, I can just do the division with that function and not worry about invalid values.

First and foremost, I agree with plog that when you start getting THAT convoluted, you are probably making a big mistake somewhere in the design.

Second, however, the answer to your question about where to put your zero-divide check?


In other words, bracket your entire expression and just don't bother to evaluate it when the divisor is zero. However, since there is more to your expression than I actually used, I actually prefer plog's comments as being more to the point. This situation screams for a dedicated computation function. Has to be a function since you need something that will return a value, of course.