What is the equivalent of Select Case in Access SQL?

by

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.

More Related Contents:

  1. Combine values from related rows into a single concatenated string value
  2. What are the rules governing usage of parenthesis in VBA function calls?
  3. Autonumber value of last inserted row – MS Access / VBA
  4. How to get random record from MS Access database
  5. create form to add records in multiple tables
  6. Operation must use an updatable query. (Error 3073) Microsoft Access
  7. How do I force VBA/Access to require variables to be defined?
  8. Hand Install of 64-bit MS Access ODBC drivers when 32-bit Office is present
  9. Running Microsoft Access as a Scheduled Task
  10. “Operation must use an updateable query” error in MS Access
  11. How to restart counting from 1 after erasing table in MS Access?
  12. Equivalent cURL in VBA?
  13. VBScript & Access MDB – 800A0E7A – “Provider cannot be found. It may not be properly installed”
  14. Run-time error ‘3061’. Too few parameters. Expected 1. (Access 2007)
  15. MS Access trigger?
  16. Insert SQL command with Datetime in MS-Access
  17. Is MS Access (JET) suitable for multiuser access?
  18. VBA procedure to import csv file into access
  19. How to select top 10 in Access query?
  20. ConcatRelated() in Access, using two keys
  21. Concatenating multiple rows into single line in MS Access [duplicate]
  22. How to write Case Sensitive Query for MS Access?
  23. Transform multiple rows into columns with unique key
  24. connected combo boxes in continuous form – the second combo doesn’t show its value if dependent on first combo how could I fix that
  25. UrlDownloadToFile in Access 2010 – Sub or Function not Defined
  26. Custom row source for combo box in continuous form in Access
  27. VBA – Run Time Error 3271 using DAO object
  28. Working with multiple programmers on MS Access
  29. Trouble with InputBoxes
  30. Get Name of Current VBA Function

Leave a Comment