Software

Ask something
Discussion Locked

(CASE Expression and Access 2003)

0 helpful
2 replies

by andeezle

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

Access 2003 allegedly supports SQL-92 (i've set it to do so), and the CASE expression is part of the SQL-92 standard (per SQL For Smarties 2nd ed., pg 127). So why then does this not work for me:
ROUND(SUM(CASE WHEN tc1.Tc_OpCode = 'r' THEN 1 ELSE 0), 3)

And what I really want to do is:
ROUND(SUM(CASE WHEN tc1.Tc_OpCode = 'r' THEN tc1.Tc_Hours ELSE 0), 3), notice that I want to return tc1.Tc_Hours rather than 0 if tc1.Tc_OpCode = 'r'.

Thanks a lot.

-Andy

2 replies

by tony hopkinson

CASE
WHEN booleanExpression1 THEN result1
WHEN booleanExpression2 THEN result2
...
WHEN booleanExpressionN THEN resultN

[
ELSE elseResult
]
END
or the simpler form

CASE expression
WHEN value1 THEN result1
WHEN value2 THEN result2
...
WHEN valueN THEN resultN

[
ELSE elseResult
]
END

by rommelmp

You can use the switch function

Switch ( expression1, value1, expression2, value2, ... expression_n, value_n )
where expression can be a condition

p.e.
Switch (SupplierID=0 or SupplierID=1, "IBM", SupplierID=2, "HP", SupplierID=3, "Nvidia")

an access sql sample:
SELECT
Switch(zip='92789', 'west', zip Is Null, 'missing', zip>='96745', 'northwest', True, '*') AS [Searched Case]
FROM authors;

also you can use the IIF function:
iif ( condition, value_if_true, value_if_false )

p.e.
iif ([Qty] > 10, "large", "small")

This discussion is closed to new replies.