IIF Statement or Formatting Issue?

  • Thread starter Thread starter Anthony Feole
  • Start date Start date
A

Anthony Feole

Hello,

I'm currently running an multiple IIF statement with > and
< conditions within a query and it works correctly.
Basically, the statement looks at 2 other field values and
calculates accordingly.

Unfortunately, when the IIF statement hits a value that
that is equal, a zero should populated as the value. When
this situation arrises I get the following value or
something like it:

7.105427357601E-15

I've tried adding >= or <= to the statement but the same
value was returned. Listed below is the entire statement:

9 - 12 Months $: IIf([WIP_SUPPLY_TYPE]="Bulk",0,IIf([OH $]
=([Average Monthly Demand $]*DLookUp
Click to expand...
("[UBL]","tblBAMOH","[BAMOHID]=4")),[Average Monthly
Demand $]*(DLookUp("[UBL]","tblBAMOH","[BAMOHID]=4")-
DLookUp("[LBL]","tblBAMOH","[BAMOHID]=4")),IIf([OH $]<=
([Average Monthly Demand ]*DLookUp
("[LBL]","tblBAMOH","[BAMOHID]=4")),"HELP",[OH $]-
([Average Monthly Demand $]*DLookUp
("[LBL]","tblBAMOH","[BAMOHID]=4")))))

Regards,

Tony
 
Unfortunately, when the IIF statement hits a value that
that is equal, a zero should populated as the value. When
this situation arrises I get the following value or
something like it:

7.105427357601E-15
Click to expand...

You're evidently storing money amounts in a Double datatype field.
Don't.

A Double is an *approximation*, storing the number you enter with an
error in about the fourteenth or fifteenth decimal place. Subtracting
two such numbers will indeed give a roundoff error of about this size.

Instead, even if the value isn't money, use a Currency datatype. This
is a scaled huge integer, with exactly four decimal places, no
roundoff error, and a range of values into the trillions. Or, rather
than testing for zero, test the absolute value of the difference to
see if it's less than 1.0E-12 or so.
 
Back
Top