[ms-access] What is the equivalent of Select Case in Access SQL?

I have a query which includes fields named openingbalance and commissions. I would like to compute values for commissions based on openingbalance, similar to this Select Case block in Access VBA:

Select Case OpeningBalance
   Case 0 To 5000
        commission = 20
   Case 5001 To 10000
        commission = 30
   Case 10001 To 20000
        commission = 40
   Case Else
        commission = 50
End Select

But since Access doesn't allow Select Case in a query, how can I accomplish my goal in Access SQL?

This question is related to ms-access select-case

The answer is


You could do below:

select
iif ( OpeningBalance>=0 And OpeningBalance<=500 , 20, 

                  iif ( OpeningBalance>=5001 And OpeningBalance<=10000 , 30, 

                       iif ( OpeningBalance>=10001 And OpeningBalance<=20000 , 40, 

50 ) ) ) as commission
from table

You can use IIF for a similar result.

Note that you can nest the IIF statements to handle multiple cases. There is an example here: http://forums.devshed.com/database-management-46/query-ms-access-iif-statement-multiple-conditions-358130.html

SELECT IIf([Combinaison] = "Mike", 12, IIf([Combinaison] = "Steve", 13)) As Answer 
FROM MyTable;