MS Query Case When syntax error

Can someone correct the syntax of the following MS Query SQL?  There is an error in the case when statement to sum the SIP field.  The SIP field is a Y/N field.

SELECT

tbl_NHSN_SSI_Pts.SurgeonID,
tbl_NHSN_SSI_Pts.Surgeon,  
Count(tbl_NHSN_SSI_Pts.SurgeonID) AS 'SURG',

sum(CASE WHEN tbl_NHSN_SSI_Pts.SIP = 'Yes' then 1 else 0 end ) as 'INF'

FROM `P:\IC-Employee Health\SSI\NHSN.accdb`.tbl_NHSN_SSI_Pts tbl_NHSN_SSI_Pts



GROUP BY tbl_NHSN_SSI_Pts.Surgeon, tbl_NHSN_SSI_Pts.SurgeonID
ORDER BY tbl_NHSN_SSI_Pts.Surgeon, tbl_NHSN_SSI_Pts.SurgeonID


Thanks

Glen
GPSPOWAsked:
Who is Participating?
 
SimonConnect With a Mentor Commented:
For a bit datatype field, use this version of the CASE syntax

sum(CASE tbl_NHSN_SSI_Pts.SIP when 1 then 1 else 0 end ) as 'INF'
0
 
SimonCommented:
By Yes/No field do you mean a bit field or a character field that stores "Yes" and "No". If the latter then your case statement should work.
0
 
GPSPOWAuthor Commented:
Yes.
0
 
GPSPOWAuthor Commented:
Thanks

I also found that if I change it to a MS-Access  IIF clause this works too.

Glen
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.