Query iiF Woes

D

DS

I have this "iif" statement in a Query that is giving me problems, quite
frankly I'm just having problems writing it. Any help is appreciated.
Thanks
DS

BDIS: IIf([CDDiscountWhere]="A",
CCur(Nz(IIf([CDDiscountDP]=1,(BPrice*(1+CDTaxRate))+[CDQuantity]*[CDDiscountAmount],
IIf([CDDiscountDP]=2,-(([CDQuantity]*[BPrice])*(1+CDTaxRate))*[CDDiscountPercent]),
[CDQuantity]*[BPrice])),
IIf(CDDiscountType="B",
CCur(Nz(IIf([CDDiscountDP]=1,[CDQuantity]*[CDDiscountAmount],
IIf([CDDiscountDP]=2,-([CDQuantity]*[BPrice])*[CDDiscountPercent],
[CDQuantity]*[BPrice])))))
 
E

Evi

When I get a long long IIF function like this, I find it really helps to
turn it into a function and putting it into a module. I often spot logic
mistakes which I couldn't see in my IIF statement.

Start off by writing it down like a piece of text without worrying too much
about the syntax, so long as you can see that it says what you want it to
say.
Go for the simplest, clearest way of writing it out rather than the
slickest.

eg

If CDDiscountWhere ="A" and CDDiscountDP = 1 Then
CDTaxRate = CDTaxRate + 1
BDIS = CCur(NZ((BPrice * CDTaxRate) + CDQuantity * CDDiscountAmount))
End If

If CDDiscountWhere = "A" and CDDiscountDP = 2 Then
CDTaxRate = CDTaxRate + 1
BDIS = -CCur(NZ(((CDQuantity*BPrice)*(CDTaxRate)) *CDDiscountPercent))
End If

If CDDiscountWhere = "B" AND CDDiscountDP = 1 Then
etc

have a go and if you need to know how, we can show you how to turn this into
a function so that you would simply write in your query


BDIS:
FindBDIS([CDDiscountWhere],[CDDiscountDP],[CDTaxRate],BPrice,[CDQuantity],[C
DDiscountAmount], [CDDiscountPercent])

Evi

DS said:
I have this "iif" statement in a Query that is giving me problems, quite
frankly I'm just having problems writing it. Any help is appreciated.
Thanks
DS

BDIS: IIf([CDDiscountWhere]="A",
CCur(Nz(IIf([CDDiscountDP]=1,(BPrice*(1+CDTaxRate))+[CDQuantity]*[CDDiscount
Amount],IIf([CDDiscountDP]=2,-(([CDQuantity]*[BPrice])*(1+CDTaxRate))*[CDDiscountPer
cent]),
[CDQuantity]*[BPrice])),
IIf(CDDiscountType="B",
CCur(Nz(IIf([CDDiscountDP]=1,[CDQuantity]*[CDDiscountAmount],
IIf([CDDiscountDP]=2,-([CDQuantity]*[BPrice])*[CDDiscountPercent],
[CDQuantity]*[BPrice])))))
Click to expand...
 
Top