Re: Divison by Zero Error (even using iif)

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Gary Walter (garylwpleasenospam_at_wamego.net)
Date: 07/29/04

  • Next message: Michael Keating: "Re: Printing Lists of Queries" 
    Date: Thu, 29 Jul 2004 14:44:28 -0500

    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)" wrote:
    > 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(...))
    >
    > Dan wrote:
    > >
    > > 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
    > >
    > >

  • Next message: Michael Keating: "Re: Printing Lists of Queries"

    Relevant Pages