What is the equivalent of Select Case in Access SQL?

Question

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?

HansUp · Accepted Answer · 2013-04-03T03:33:09.417

93

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.

edited Apr 03 '13 at 03:33

answered Apr 02 '13 at 23:59

HansUp

95,961
11
77
135

3

HansUp you are the greatest. The switch function works great. Thanks – Kelly K. Apr 10 '13 at 17:16
Is `SWITCH()` one of those VBA functions that you can only use in Access SQL *when executed from within* Access itself (like `Nz()`), or does it also work when you use the JET Red OLE-DB/ODBC or ACE driver? – Dai Sep 11 '20 at 03:32
1

Since it's [available in sandbox mode](https://support.microsoft.com/en-us/office/functions-and-properties-in-access-blocked-by-sandbox-mode-9a829783-f7a8-4a9f-8d43-8650b8cc9565#bm2), I think it should work in any query whether or not the query is run from within an Access session. – HansUp Sep 13 '20 at 15:57

score 9 · Answer 2 · answered Apr 02 '13 at 20:53

9

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;

answered Apr 02 '13 at 20:53

D'Arcy Rittich

167,292
40
290
283

3

Thanks for the response RedFilter, this is perfect for another query I am working on where I only needed to combine 2 if statement. Since I just started using Access 4 months ago I think the switch function HansUp suggested is easier to read. – Kelly K. Apr 10 '13 at 17:34
Very late to the party, but worthy of note: care should be taken when using `IIf` due to the fact that *all parts (test, true result and false result) are all executed during call*. So, if you have any function calls in your `IIf`, for instance - `SELECT IIf(Process([Combinaison]) = "Mike", 12, IIf(Process([Combinaison]) = "Steve", 13)) As Answer ...`, and those functions *change* any values, then the effect will happen twice - once for each call. https://learn.microsoft.com/en-us/office/vba/Language/Reference/user-interface-help/iif-function#remarks – Paul Dec 20 '21 at 08:57

score 2 · Answer 3 · answered Apr 02 '13 at 20:55

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

What is the equivalent of Select Case in Access SQL?

3 Answers3

Linked

Related