CASE statement possible in Access?

[LloydDobler]

Hi everyone, I'm trying to use a query from SQL in Access and am wondering if this is possible.

SELECT division, SUM(CASE datepart('y', [date initiated]) WHEN datepart('y', getdate()) THEN 1 ELSE 0 END) AS CHYTD,
SUM(CASE datepart('m',[date initiated]) WHEN 1 THEN containmenthrs ELSE 0 END) AS CHJAN, etc through December

I checked the help files to see if CASE can be used in Access, but it says check the SQL documentation, which leads me to believe it can, so I'm wondering if it's my syntax here?


"I don't want to sell anything, buy anything or process anything as a career. I don't want to sell anything bought or processed... or buy anything sold or processed... or process anything sold, bought or processed... or repair anything sold, bought or processed. You know, as a career, I don't want to do that."

 

[dhookom]

You can use IIf(), Switch(), and Choose() in Access queries. A brief review of your sql suggests you might be able to use a Crosstab query to get your results.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]