division by zero error 1

[JCAA]

Can someone help me and tell me what I can do not to get a division by zero error on the following query

PhaseOutCalc: IIf([PhaseOut]="PhaseOutValue",[300 - For Surplus and Obsolete Valuation 2]![OH Qty]/([300 - For Surplus and Obsolete Valuation 2]![OnHand-Year]))

THANKS!!

 

[BobAtHome]

You will need to check if the divisor is equal to zero as part of your in-line if criteria statement. You will also need to supply a value if the statement is false.

PhaseOutCalc: IIf([PhaseOut]=&quot;PhaseOutValue&quot; and ([300 - For Surplus and Obsolete Valuation 2]![OnHand-Year]) <> 0,[300 - For Surplus and Obsolete Valuation 2]![OH Qty]/([300 - For Surplus and Obsolete Valuation 2]![OnHand-Year]), put false value here)

 

[JCAA]

THANKS!!

 

[RJFost]

Also, remember that- unlike an If...Else...EndIf structure- both results are evaluated in an IIf, not just the one whose condition is met. In other words, regardless of the result of the [PhaseOut]=&quot;PhaseOutValue&quot; test, both the True & the False results are evaluated. I've seen divide-by-zero bite people who weren't aware of this quite a few times.

 

[Golom]

RJFost

True enough in code ... however when the IIF is in an SQL statement it doesn't seem to have that problem. Just another &quot;feature&quot; from MS.

 

[RJFost]

Is that &quot;undocumented feature&quot; version-dependent? I've just gotten in the habit of slinging code that isn't susceptible to the problem, but haven't checked to see if the problem is still there. Checking the documentation for Access 2000, under the IIf definition it still warns: &quot;IIf always evaluates both <truepart> & <falsepart>, even though it only returns one of them...if evaluating <falsepart> results in a division-by-zero error, an error occurs even if <expr> is true.&quot;

I always assumed that, if the warning was still there, so was the problem, & just coded around it.

 

[Golom]

Just as a test, I did the following.

In VB

   x = 0
   IIF ( X = 0, 0, 5/x )

And, sure 'nuff, Run Time error 11: Division By Zero.

Then I did this in Access

SELECT X, Q, IIf(Q=0, 0, X/Q) AS Div
FROM A;

The value of &quot;Q&quot; was zero on 2 records and it reported &quot;0&quot; for &quot;Div&quot; on those records without any complaints.

 

[RJFost]

&quot;You live & learn, or you don't live long.&quot; Thanks for the update.