how to avoid #Div/0! error

[lugnutmonkey]

I have a query that returns the Delta % for pair of data points. I use this information in a report. Due to the nature of the report some data points have a zero value. This is not a problem when the zero is the second value as it just returns an unusual % change. The real problem for my managers is the #Div/0! error message that comes up when it attempts to divede by zero.

 Delta: ([Test1]-[Test2])/[Test1]))

What would I have to add to the above expression to avoid the division by 0 error?

[Robeen]

Have you considered using an IIF (,,) Statement?

[lugnutmonkey]

I have, I don't know how I would use the error as the arugement so that if it was a #Div/0! error it would display "0" or some pre-defined text.

[cphelps]

Unless there is more to your requirements you could make an assumption that negates the need to check for an error by using an Iif statement like =IIF([Test1]=0,0,([Test1]-[Test2])/[Test1]).

[June7]

cp, that expression should still error. Reason is all parts of IIf must be able to evaluate, even if it is not the part that returns result. So there is still an expression that will try to divide by 0 and the #Div/0! error will trigger.

Delta: ([Test1]-[Test2])/IIf([Test1]=0,1,[Test1])

or

Delta: IIf([Test1]=0,"No Value", ([Test1]-[Test2])/IIf([Test1]=0,1,[Test1]))

[lugnutmonkey]

Thank you June7, They both work. I prefer the second solution becasue it provides the ability to deliver a message in place of arbitrary result. the only problem I am having with it is that the Percent formatting I hade on the field is lost when one of the results of the IIF function is a calculated result.

Is there anyway that I could dictate the format of the result through code as opposed to using the properties menu?

[lugnutmonkey]

Answered my own question, FormatPercent(, sorry to bother you.

Thanks for all the help!!!