IIF Query Problem

[law32908]

Hello all, this is my first question. Thanks in advance for any help you can provide.

I have a statement in a query that should read like this:

If Warn8=Yes, then (Full*AWP)+(9Percent/100)*AWP))+((AWP/Packagesize)*Exact))

If Warn8=No, then (Full*AWP)+(9Percent/100)*AWP))+((AWP/Packagesize)*Exact))*0.85

The only difference between the two is the multiplication of the 0.85.

I have it written like this:

ERV: IIf([WARN8]=Yes,(([FULL]*[AWP])+(([PERCENT]/100)*[AWP])+(([AWP]/[PACKAGESIZE])*[EXACT])),(([FULL]*[AWP])+(([PERCENT]/100)*[AWP])+((([AWP]/[PACKAGESIZE])*[EXACT])*0.85)))

When I run the query, the ELSE section does not multiply by 0.85. It just looks at it like Warn8 = yes.

Help??

Laura

 

[ToeShot]

I have a question if the only thing that is different is the .85 can you assign this equation to a variable then you can do iif(warn8 = yes, variable, variable*.85) Dave
ToeShot@Hotmail.com
Today Is Tomorrows Yesterday. So Why Wait

 

[Quehay]

I hate counting parentheses...the TRUE section doesn't need wrapper parentheses because it's addition of components (the * and / sections need the () ). The FALSE section does need a wrapper prior to the * .85

Here's what I get, although I think it's basically the same as what you had already, with the removal of unneeded '=YES':

**It's ambiguous, to my eyes anyway, whether you are multiplying the entire subgroup by EXACT or not

[tt]ERV: Iif(
[WARN8],
([FULL]*[AWP])+([PERCENT]/100)*[AWP])+([AWP]/[PACKAGESIZE])*[EXACT]),
((([FULL]*[AWP])+([PERCENT]/100)*[AWP])+([AWP]/[PACKAGESIZE])*[EXACT]))
*0.85))
)
[/tt]

This should work. If it still refuses, then maybe a sub-query where you get OrderID and Discount:
[tt]
SELECT
OrderID,
(([FULL]*[AWP])+([PERCENT]/100)*[AWP])+([AWP]/[PACKAGESIZE]))*[EXACT] AS YourName,
iif([WARN8],1,.85) AS DISCOUNT
from tables

Then use this in next level, joining on Order ID and multiplying Result: ([YourName]) * [Discount])[/tt]