Divison by Zero Error (even using iif)

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
 
G

Gerald Stanley

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
 
D

Dan

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...
 
J

John Spencer (MVP)

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(...))
 
K

Ken Snell

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] &
"'")))))
 
D

Dan

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...
 
G

Gary Walter

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...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Division by Zero Error 1
IIF statement 1
divison by zero 2
Divide by Zero error 3
Divide By Zero Error 2
Data Conversion Expression returns #Error 2
Update Query "IIf" and "Right" Function 4
Division by Zero Error 4

Top