Divison by Zero Error (even using iif)

  • Thread starter Thread starter Dan
  • Start date Start date
D

Dan

Ok...I have the following field in a query that returns a
DIVIDE by ZERO error:

Field4: Sum([Units]/(DLookUp
("[FieldA]","[Table1]","[FieldB] = '" & [Field1] & "'")))

It is the Dlookup that sometimes returns a zero and hence
the error.....so to try to avoid the error I am using an
iif statement..

Example
iif(DLookUp("[FieldA]","[Table1]","[FieldB] = '" &
[Field1] & "'") = 0, 0, Sum([Units]/(DLookUp
("[FieldA]","[Table1]","[FieldB] = '" & [Field1] & "'"))))

This should return 0 when my divisor is 0 else return the
function value....However I've noticed and later read
that "The IIf function always evaluates both the truepart
and the falsepart, even though it returns only one
value. Because of this, you should watch for undesirable
side effects. For example, if evaluating the falsepart
results in a division by zero error, an error occurs even
when the expression is True."

So is there a workaround to this? Please help!!! Any
suggestions are greatly appreciated!

Thanks,
Dan
 
Try the Switch function instead of IIf
E.g.

field4:Switch(DLookUp("[FieldA]","[Table1]","[FieldB] = '"
& [Field1] & "'") = 0, 0,
DLookUp("[FieldA]","[Table1]","[FieldB] = '" &
[Field1] & "'") <> 0,Sum([Units]/(DLookUp
("[FieldA]","[Table1]","[FieldB] = '" & [Field1] & "'"))))

Hope This Helps
Gerald Stanley MCSD
 
I get the same error with the switch function as well :(
According to Access Help the switch function also
evaluates all of the expressions, even though it returns
only one of them.
-----Original Message-----
Try the Switch function instead of IIf
E.g.

field4:Switch(DLookUp("[FieldA]","[Table1]","[FieldB] = '"
& [Field1] & "'") = 0, 0,
DLookUp("[FieldA]","[Table1]","[FieldB] = '" &
[Field1] & "'") <> 0,Sum([Units]/(DLookUp
("[FieldA]","[Table1]","[FieldB] = '" & [Field1] & "'"))))

Hope This Helps
Gerald Stanley MCSD
-----Original Message-----
Ok...I have the following field in a query that returns a
DIVIDE by ZERO error:

Field4: Sum([Units]/(DLookUp
("[FieldA]","[Table1]","[FieldB] = '" & [Field1] & "'")))

It is the Dlookup that sometimes returns a zero and hence
the error.....so to try to avoid the error I am using an
iif statement..

Example
iif(DLookUp("[FieldA]","[Table1]","[FieldB] = '" &
[Field1] & "'") = 0, 0, Sum([Units]/(DLookUp
("[FieldA]","[Table1]","[FieldB] = '" & [Field1] & "'"))))

This should return 0 when my divisor is 0 else return the
function value....However I've noticed and later read
that "The IIf function always evaluates both the truepart
and the falsepart, even though it returns only one
value. Because of this, you should watch for undesirable
side effects. For example, if evaluating the falsepart
results in a division by zero error, an error occurs even
when the expression is True."

So is there a workaround to this? Please help!!! Any
suggestions are greatly appreciated!

Thanks,
Dan


.
Click to expand...
.
Click to expand...
 
First is there a chance that your DLookup is returning a NULL value not a ZERO
value. If there is no match, you will get Null returned.

Immediate If (IIF) in a query doesn't evaluate both the truepart and falsepart
sections. In VBA, it does. (Implemented differently for whatever reason).

Try using the NZ function to force a zero when null is returned and then test
that:

IIF(NZ(DLookup(...),0)=0,0,Sum(Units/DLookup(...))
 
Try this (kludgy, I know):

IIf(DLookUp("[FieldA]","[Table1]","[FieldB] = '" & [Field1] & "'") = 0, 0,
Sum([Units]/(IIff(DLookUp("[FieldA]","[Table1]","[FieldB] = '" & [Field1] &
"'") = 0, 1, DLookUp("[FieldA]","[Table1]","[FieldB] = '" & [Field1] &
"'")))))
 
Though kludgy...at least it works! Thanks!

-----Original Message-----
Try this (kludgy, I know):

IIf(DLookUp("[FieldA]","[Table1]","[FieldB] = '" & [Field1] & "'") = 0, 0,
Sum([Units]/(IIff(DLookUp
Click to expand...
("[FieldA]","[Table1]","[FieldB] = '" & [Field1] &
"'") = 0, 1, DLookUp("[FieldA]","[Table1]","[FieldB] = '" & [Field1] &
"'")))))

--

Ken Snell
<MS ACCESS MVP>

Dan said:
Ok...I have the following field in a query that returns a
DIVIDE by ZERO error:

Field4: Sum([Units]/(DLookUp
("[FieldA]","[Table1]","[FieldB] = '" & [Field1] & "'")))

It is the Dlookup that sometimes returns a zero and hence
the error.....so to try to avoid the error I am using an
iif statement..

Example
iif(DLookUp("[FieldA]","[Table1]","[FieldB] = '" &
[Field1] & "'") = 0, 0, Sum([Units]/(DLookUp
("[FieldA]","[Table1]","[FieldB] = '" & [Field1] & "'"))))

This should return 0 when my divisor is 0 else return the
function value....However I've noticed and later read
that "The IIf function always evaluates both the truepart
and the falsepart, even though it returns only one
value. Because of this, you should watch for undesirable
side effects. For example, if evaluating the falsepart
results in a division by zero error, an error occurs even
when the expression is True."

So is there a workaround to this? Please help!!! Any
suggestions are greatly appreciated!

Thanks,
Dan
Click to expand...


.
Click to expand...
 
I have to "me too" with John.

The Expression Service isn't supposed to evaluate
both parts, but only the part determined by
the initial condition (as opposed to VBA where
you should just use If/Then/Else anyway).

What happens if you put the Sum outside the IIF?

Field4: SUM(IIF(DLookUp("[FieldA]","[Table1]","[FieldB] = '" &
[Field1] & "'") = 0, 0, ([Units]/(DLookUp
("[FieldA]","[Table1]","[FieldB] = '" & [Field1] & "'")))))

or change to (if FieldA cannot be negative):

Field4: SUM(IIF(DLookUp("[FieldA]","[Table1]","[FieldB] = '" &
[Field1] & "'") > 0, ([Units]/(DLookUp
("[FieldA]","[Table1]","[FieldB] = '" & [Field1] & "'"))), 0))



John Spencer (MVP) said:
First is there a chance that your DLookup is returning a NULL value not a ZERO
value. If there is no match, you will get Null returned.

Immediate If (IIF) in a query doesn't evaluate both the truepart and falsepart
sections. In VBA, it does. (Implemented differently for whatever reason).

Try using the NZ function to force a zero when null is returned and then test
that:

IIF(NZ(DLookup(...),0)=0,0,Sum(Units/DLookup(...))
Ok...I have the following field in a query that returns a
DIVIDE by ZERO error:

Field4: Sum([Units]/(DLookUp
("[FieldA]","[Table1]","[FieldB] = '" & [Field1] & "'")))

It is the Dlookup that sometimes returns a zero and hence
the error.....so to try to avoid the error I am using an
iif statement..

Example
iif(DLookUp("[FieldA]","[Table1]","[FieldB] = '" &
[Field1] & "'") = 0, 0, Sum([Units]/(DLookUp
("[FieldA]","[Table1]","[FieldB] = '" & [Field1] & "'"))))

This should return 0 when my divisor is 0 else return the
function value....However I've noticed and later read
that "The IIf function always evaluates both the truepart
and the falsepart, even though it returns only one
value. Because of this, you should watch for undesirable
side effects. For example, if evaluating the falsepart
results in a division by zero error, an error occurs even
when the expression is True."

So is there a workaround to this? Please help!!! Any
suggestions are greatly appreciated!

Thanks,
Dan
Click to expand...
Click to expand...
 
Back
Top