IIF statements with And

[slimjen1]

All,

I am trying to make an IIF statement with three possible outcomes and not working:

IIf([status]="Active" and [Type]= “Cost”,[rate1]-[rate2]+[ rate4],IIf([status]="InActive",[ rate1]+[rate3],[ rate2]+[rate3])) AS newRate

It tells me i'm missing parathesis or wrong argument. I keep changing to make it correct but still getting errors. Can someone look at it and make suggestions please. Thanks

 

[plog]

What errors are you getting?

I see that you have spaces before some field names that are probably incorrect:

[ rate2] and [rate2]

Do you actually have 2 different fields?

 

[slimjen1]

the space is a typo. I'm getting wrong number of arguments or missing parenthesis

 

[JHB]

Maybe it is a typo also, but your quotes is of a wrong type!

IIf([status]="Active" and [Type]= [B][COLOR=Red]“[/COLOR][/B]Cost[B][COLOR=Red]”[/COLOR][/B],[rate1]-[rate2]+[ rate4],IIf([status]="InActive",[ rate1]+[rate3],[ rate2]+[rate3])) AS newRate

 

[Minty]

I agree with JHB
Also it sometimes helps to put each bit in brackets to clarify the process

IIf([status]="Active" and [Type]= "Cost",([rate1]-[rate2]+[rate4]),(IIf([status]="InActive",[rate1]+[rate3],[rate2]+[rate3]))) AS newRate

 

[slimjen1]

thanks all. I'll change and post back.

 

[slimjen1]

Ok. I put extra parenthesis around and it worked. But then I realized I forgot the else in the first part and now it tells me the "," is incorrect.

IIf([status]= "Active" and [Type]= "Cost",([rate1]-[rate2]+[rate4[B]],[rate1][/B]),(IIf([status]="InActive",[rate1]+[rate3],[rate2]+[rate3])) AS newRate

 

[Minty]

You didn't forget it, the else IS the second iif...