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?
Solution 1:
Consider the Switch Function as an alternative to multiple IIf()
expressions. It will return the value from the first expression/value pair where the expression evaluates as True, and ignore any remaining pairs. The concept is similar to the SELECT ... CASE
approach you referenced but which is not available in Access SQL.
If you want to display a calculated field as commission
:
SELECT
Switch(
OpeningBalance < 5001, 20,
OpeningBalance < 10001, 30,
OpeningBalance < 20001, 40,
OpeningBalance >= 20001, 50
) AS commission
FROM YourTable;
If you want to store that calculated value to a field named commission
:
UPDATE YourTable
SET commission =
Switch(
OpeningBalance < 5001, 20,
OpeningBalance < 10001, 30,
OpeningBalance < 20001, 40,
OpeningBalance >= 20001, 50
);
Either way, see whether you find Switch()
easier to understand and manage. Multiple IIf()s
can become mind-boggling as the number of conditions grows.
Solution 2:
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;
Solution 3:
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