What is the equivalent of Select Case in Access SQL?

ms-access, select-case

Solution

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.

Problem

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?

Original source