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

User · Accepted Answer

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.

User · Answer

You could do below   select iif   OpeningBalance gt  0 And OpeningBalance lt  500   20                      iif   OpeningBalance gt  5001 And OpeningBalance lt  10000   30                           iif   OpeningBalance gt  10001 And OpeningBalance lt  20000   40    50       as commission from table

User · Answer

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

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

Examples related to ms-access

Examples related to select-case