Case expressions in Access
You can use the IIF()
function instead.
IIF(condition, valueiftrue, valueiffalse)
condition
is the value that you want to test.valueiftrue
is the value that is returned if condition evaluates to TRUE.valueiffalse
is the value that is returned if condition evaluates to FALSE.
There is also the Switch
function which is easier to use and understand when you have multiple conditions to test:
Switch( expr-1, value-1 [, expr-2, value-2 ] … [, expr-n, value-n ] )
The Switch function argument list consists of pairs of expressions and
values. The expressions are evaluated from left to right, and the
value associated with the first expression to evaluate to True is
returned. If the parts aren't properly paired, a run-time error
occurs. For example, if expr-1 is True, Switch returns value-1. If
expr-1 is False, but expr-2 is True, Switch returns value-2, and so
on.Switch returns a Null value if:
None of the expressions is True.
The first True expression has a corresponding value that is Null.
NOTE: Switch evaluates all of the expressions, even though it returns only one of them. For this reason, you should watch for
undesirable side effects. For example, if the evaluation of any
expression results in a division by zero error, an error occurs.
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.
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")
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)
MS Access SUMIIF to SQL CASE statement
Your case when syntax is wrong :
SELECT Project.ProjectName,
cmp_UKWarranty.ProjectID,
Sum(CASE
WHEN ( cmp_UKWarranty.ContactID > 0 ) THEN 1
ELSE 0
END)
FROM cmp_UKWarranty
INNER JOIN Project
ON cmp_UKWarranty.ProjectID = Project.ProjectID
INNER JOIN Dial
ON cmp_UKWarranty.DialID = Dial.DialID
CROSS JOIN CRC
using CASE WHEN statements in Microsoft Access
MS Access doesn't support case statements. Instead you can use the "immediate if" function (iif) and nest them for multiple conditions:
where prestiti.durata < iif (prestiti.rateizzazione = "mensile", 60,
iif (prestiti.rateizzazione = "annuale", 5, 0))
Microsoft Access - Case Query
There is no CASE ... WHEN in Access SQL. You can use the Switch Function instead.
UPDATE HAI
SET REGION = Switch(
NUMREG Like '*1', 'BDG',
NUMREG Like '*2', 'JKT',
NUMREG Like '*3', 'KNG'
);
That query uses Access' default (ANSI 89 mode) * instead of % wildcard character. If you want to use the % wildcard, you can do it with the ALike comparison operator.
UPDATE HAI
SET REGION = Switch(
NUMREG ALike '%1', 'BDG',
NUMREG ALike '%2', 'JKT',
NUMREG ALike '%3', 'KNG'
);
ms access sql - select case when in select subquery
MS Access doesn't support case
expressions. You can use IIF()
instead:
select d.person, max(d.score) as maxScore,
iif(d.person in (select d2.person from data as d2 where d2.flag = 'yes'), 1, 0) as flagYes
from data as d
group by person
Related Topics
Update Multiple Rows in Same Query Using Postgresql
Creating a "Numbers Table" in MySQL
T-SQL Datetime Rounded to Nearest Minute and Nearest Hours With Using Functions
Dynamic SQL - Exec(@Sql) Versus Exec Sp_Executesql(@Sql)
How to Run a SQL Query on an Excel Table
Getting Only Month and Year from SQL Date
SQL How to Make Null Values Come Last When Sorting Ascending
Sql: Repeat a Result Row Multiple Times, and Number the Rows
When to Use Common Table Expression (Cte)
Copy Tables from One Database to Another in SQL Server
Restore Table Structure from Frm and Ibd Files
How to Order by With Union in Sql
Return Multiple Columns of the Same Row as Json Array of Objects
Reference Alias (Calculated in Select) in Where Clause
How to Turn Identity_Insert on and Off Using SQL Server 2008