IIF clause 2

[samotek]

i get a syntax error with the following IIf clause

NewPrice: IIf([Size]=1;100,IIf([Size]=4;300))*[ExtendedPrice]

My condition is the following : if [size] = 1 then extended price = 100 * [extendedprice, if [size] = 4 then
extendedprice = 300 * [extendedprice]

Can you help me find the error ?

 

[lespaul]

this will work once you put in what value you want if size is NOT 1 or 4? What if size = 2 or 3 or 6? that value needs to replace the ???? for the "ELSE" clause:

iif(size = 1, 100 * [extendedprice], iif(size = 4, 300 * [extendedprice], ????))



Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[PHV]

Depending of your regional settings, either:
NewPrice: IIf([Size]=1;100;IIf([Size]=4;300;0))*[ExtendedPrice]

Or:
NewPrice: IIf([Size]=1,100,IIf([Size]=4,300,0))*[ExtendedPrice]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[samotek]

it works great !!!!! Thank you so so much !!!!!!
I took the first alternative suggested depending on the my regional setting and everything clicks wonderful, hated "syntax error " does not appear any more.
Thanks !!!!!

 

[samotek]

Oh how right is Leslie ! My expression works perfectly with the help of PH in the following way :
IIf([Size]=1;0,138;IIf([Size]=4;0,552;0))*[ExtendedPrice]

But i have forgotten the cases when size is not 1 or 4.
Up to now i get nothing in the expression.
I want in these cases the newPrice to be = extendedprice.
In other words, if size = 1, the extendedprice to by multiplied by 0,138 but otherwise the extendedprice should remain the same, and not to get 0 in the expression.
Can you help me ?

 

[PHV]

IIf([Size]=1;0,138;IIf([Size]=4;0,552;[!]1[/!]))*[ExtendedPrice]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886