CASE instruction

[savix]

Hi everybody,
I'm having quiete a problem with the case instruction. I have the folowing query:

Select station, count(case poc_1 when 1 then 1 end), count(case poc_1 when 2 then 1 end)
From poc
group by station


Well Acces tells me there is a syntax error in the case instruction???Can't find!!!Can anybody help? Tanx

 

[Mile-O]

There is no CASE keyword in Access SQL.

 

[savix]

is there any alternative?

 

[Mile-O]

Since I'm not aware of how it works in the SQL context (I've not used MS SQL Server) I can't say although there will be others that can. It's possible that you can write a function in VBA and reference that in your SQL.

 

[Pat Hartman]

You can use the IIf() function.

Select station, Sum(IIf(case poc_1 = 1,1,0)) As Count1, Sum(IIf(case poc_1 =2, 1, 0)) As Count2
From poc
group by station;