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.
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)
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
Case Statement - using BETWEEN. Ms Access to SQL Server
The Switch function doesn't take SQL syntax. So:
Switch([Age] <= 17, '0-17',
[Age] <= 30, '18-30',
[Age] <= 45, '30-45',
[Age] <= 60, '45-60',
[Age] > 60, 'Over 60')
CASE statement in Access 2013 (and how to use it)?
Thanks for Gord Thompson for giving me a clue, this is what I was looking for, it's working perfect :
expr1: Switch([date_]=Date(),"today",
[date_]=Date()-1,"yesterday",
[date_]>Date()-7,"week ago")
Related Topics
How Can SQL Create Duplicate Records
Oracle: Similar to Sysdate But Returning Only Time and Only Date
SQL Server 2005 - Order of Inner Joins
Can You Do a Select on the Results of a Stored Procedure in T-Sql
Sum of Digits of a Number in SQL Server Without Using Traditional Loops Like While
SQL Server:Find Duplicates in a Table Based on Values in a Single Column
Create a SQL View Based Converting Ranges into Rows
Oracle 11G: Default to Static Value When Query Returns Nothing
Inserting New Columns in the Middle of a Table
Dynamic SQL Error Converting Nvarchar to Int
How to Have Alphanumeric Sequence Generator in SQL
Help with SQL Server Trigger to Truncate Bad Data Before Insert
Fastest Way to Check If a Character Is a Digit
Dynamic Query to Union Multiple Databases
Insert or Select Strategy to Always Return a Row
Linq to SQL "Not Like" Operator
SQL Script to Change All Table References in All Stored Procedures