What is the equivalent of Select Case in Access SQL?
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.
Does MS Access support "CASE WHEN" clause if connect with ODBC?
You could use IIF
statement like in the next example:
SELECT
IIF(test_expression, value_if_true, value_if_false) AS FIELD_NAME
FROM
TABLE_NAME
MS Access Query with CASE statement
MS Access does not support CASE
statements. Use IIF
:
IIF(tsl.activitat <> '', tsl.activitat, ts.activitat ) AS Activitat
I'm not sure if Access supports aliases on LEFT JOIN
either, but it probably does
Note (although correctly tagged), the title of your question might be troublesome... most people refer to Transact-SQL (TSQL) when writing "MS SQL". Transact-SQL is used by MS SQL Server, but not by MS Access, which uses its own SQL dialect (called "Access SQL", and pretty limited in comparison)
Related Topics
Sql to Find Upper Case Words from a Column
Mysql - How to Use Like on Multiple Columns
Max and Min Sal With Employee Name in One Query
Select Rows Within Last Complete Minute
Nodejs, MySQL - Json Stringify - Advanced Query
How to Store Emoji Character in MySQL Database
How to Minus Current and Previous Value in SQL Server
Postgresql - Combining Multiple Rows With Several Attributes into One Row
Select Multiple Columns Count One Column and Group by One Column in One Table
Joining Tables from Different Servers
Get Last Record of a Table in Postgres
Check If a Time Is Between Two Times (Time Datatype)
Sql Server - Join Rows into Comma Separated List
What Is and How to Remove Tablespace Error from My Database
Regex Pattern Inside SQL Replace Function