Discuss Using a CASE statement in MS Access in the Database Management forum on Dev Shed. Using a CASE statement in MS Access Database Management forum discussing non-database specific SQL. Structured Query Language was designed to be a robust and standardized language for manipulating relational databases.
Posts: 981
Time spent in forums: 5 Days 11 h 25 m 6 sec
Reputation Power: 41
Using a CASE statement in MS Access
(I wish there were an MS Access forum here!)
Anyway, I'm trying to run the following query in Access 2003, but it keeps telling me there's a syntax error: missing operator. Can someone decipher?
SELECT
CASE when
A.FACILITY = "This" and B.CT_INS_TYPE = "Commercial" then count(B.account_ID) else "None" end as Count_of_ID
FROM Table1 A INNER JOIN Table2 B ON Table1.ACCOUNT_ID = Table2.ACCOUNT_ID
Posts: 751
Time spent in forums: 1 Week 2 h 56 m 5 sec
Reputation Power: 348
You may need to use the IIF() function instead:
Code:
SELECT
SUM(IIF(A.FACILITY = "This"
and B.CT_INS_TYPE = "Commercial", 1, 0)) as Count_of_ID
FROM Table1 A INNER JOIN Table2 B
ON Table1.ACCOUNT_ID = Table2.ACCOUNT_ID
Posts: 981
Time spent in forums: 5 Days 11 h 25 m 6 sec
Reputation Power: 41
Quote:
Originally Posted by LKBrwn_DBA
You may need to use the IIF() function instead:
Code:
SELECT
SUM(IIF(A.FACILITY = "This"
and B.CT_INS_TYPE = "Commercial", 1, 0)) as Count_of_ID
FROM Table1 A INNER JOIN Table2 B
ON Table1.ACCOUNT_ID = Table2.ACCOUNT_ID
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
