Microsoft Access - Query IIf expression using multiple conditions

Asked By D on 08-Feb-12 09:28 PM
I'm trying to use the query expression to builder for the following:

If the "Value" field is between >50 to 100 the "MinimumBid" field = 20
If the "Value" field is between >100 to 250 the "MinimumBid" field = 30
If the "Value" field is between >250 to 500 the "MinimumBid" field = 50
If the "Value" field is between >500 to 1000 the "MinimumBid" field = 75
If the "Value" field is between >1000 to 2000 the "MinimumBid" field = 100

I have the first part working:  AI Minimum Bid: IIf([AI Value]<50,10) but when I try to add on the above conditions the query doesn't work.

How do I add on the other conditions?
[)ia6l0 iii replied to D on 08-Feb-12 10:10 PM
My advice would be to use a CASE statement instead, which makes it more readable. Nested If's are always confusing. :(
D replied to [)ia6l0 iii on 08-Feb-12 10:12 PM
Sorry I have no clue what a case statement is...help please.
Web Star replied to D on 08-Feb-12 11:32 PM
CASE statement is better use instead of  multiple if in anywhere.
if you want to do this in access than easily done just view the syntax of CASE how can it work in this link
http://www.techonthenet.com/access/functions/advanced/case.php 
Pat Hartman replied to D on 09-Feb-12 12:41 AM
In Access SQL you would use an IIf() function.  The IIf() has the basic syntax:
IIf(condition, true action, false action)  Either or both the true and false actions can be nested.  So your statement would look like:
Select ... IIf(somefield<=100,20, IIf(somefield <= 250, 30, IIf(somefield  <= 500, 50, IIf(somefield <= 1000, 75, 100)))) as NewValue,,,,
From ....

I abbreviated the conditions because when used in a query, the conditions in the IIf() are executed in order and as soon as a true condition is followed to the end, the IIf() is exited.

FYI - IIf stands for immediate If.
D replied to Pat Hartman on 09-Feb-12 08:43 PM
Thanks - works great