General discussion

Locked

(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.

-Andy

This conversation is currently closed to new comments.

2 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

You' missed END out at a guess

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

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

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

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")

Back to Software Forum
2 total posts (Page 1 of 1)  

Related Discussions

Related Forums