access sql syntax error

[SMAlvarez]

As for I know this is a valid sql statement, but for some reason access gives me a syntax error. How can I fix it? Thanks.

SELECT SUM(CASE WHEN City = 'Sandnes' THEN 1 ELSE 0 END) as FieldNeeded1
FROM Persons;

 

[JoeAtWork]

I doubt that you can put a Case statement inside a Sum.

 

[pjm]

Hi

The Access equivalent would be:-

select sum (iif (city = 'Sandnes', 1, 0)) as fieldneeded1
from persons;

pjm

 

[dhookom]

Or

SELECT Sum(Abs(city = 'Sandnes')) AS fieldneeded1
FROM persons;

Duane
Hook'D on Access
MS Access MVP

 

[pjm]

...or

select count (*) as fieldneeded 
from persons
where city = 'Sandnes';

pjm