Conditional Statement Help

[lwilt]

I'm trying to run a conditional statement in an access query. The options would be:

0-100 5
101-200 10
201-300 15

ect...something like that.

I tried IIF(([FIELD]>201),15,IIF([FIELD]>=101) and ([FIELD]<201),10))

But it's not working...any help would be great

 

[lwilt]

got it to work

Pt: IIf(([Avg Order]>601),5,(IIf([Avg Order]>=351 And ([Avg Order]<601),4)))

but now I can't get it to allow me to continuing the nested statements...help

 

[lwilt]

tried
Pt: IIf(([Avg Order]>=601),5,(IIf([Avg Order]>=351 And ([Avg Order]<601),4)),(IIF([Avg Order]>=201 And ([Avg Order]<351),3))

didnt work

 

[Joe4]

Once an IIF statement condition is True, it stops there, so you do not need the "AND" part of your formulas. Just keep moving from bigger to smaller, i.e.

[COLOR=#333333]Pt: IIf([Avg Order]>=601),5,IIf([Avg Order]>=351,4,IIF([Avg Order]>=201,3,...)))[/COLOR]

 

[lwilt]

Could you show me where I messed up my statement...keep getting an error trying to put it in and can't figure where I screwed up.

Pt:IIF([Avg Order]>=601),5,IIF(Avg Order]>=351,4,IIF([Avg Order]>=201,3,IIF([Avg Order]>=100,2,IIF([Avg Order]>0,1))))

 

[Joe4]

You have a right parend in the middle of your first IIF statement, which shouldn't be there.
You are also fish the left square bracket in front of "Avg Order" in your second IIF.
Lastly, you do not have a False condition in your last IIF (what to return if none of the conditions are met). You can leave that blank, if you like, but if you do, it will return FALSE if no conditions are met.

So your formula should look like this:

Pt:IIF([Avg Order]>=601,5,IIF([Avg Order]>=351,4,IIF([Avg Order]>=201,3,IIF([Avg Order]>=100,2,IIF([Avg Order]>0,1,0)))))

Note I have it returning 0 if none of the conditions are met.

 

[lwilt]

worked like a champ...thank you