General discussion


(CASE Expression and Access 2003)

By andeezle ·
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.


This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Comments

Collapse -

You' missed END out at a guess

by Tony Hopkinson In reply to (CASE Expression and Acce ...

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

ELSE elseResult
or the simpler form

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

ELSE elseResult

Collapse -

CASE Expression and Access 2003

by rommelmp In reply to (CASE Expression and Acce ...

You can use the switch function

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

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

an access sql sample:
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 )

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

Related Discussions

Related Forums