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 · 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

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  lt  5001  20          OpeningBalance  lt  10001  30          OpeningBalance  lt  20001  40          OpeningBalance  gt   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  lt  5001  20          OpeningBalance  lt  10001  30          OpeningBalance  lt  20001  40          OpeningBalance  gt   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 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