Query returning wrong data

[livvie]

This query is returning records that have leaving dates - it works ok if it is just LEAVEDATE IS NULL but it doesn;t work when I add the other criteria.

SELECT REF, INITIALS + ' ' + SURNAME AS Employee, DEPTCODE, LEAVEDATE
FROM dbo.vwAllEmps
WHERE (LEAVEDATE IS NULL) AND (DEPTCODE = 'CNCM') OR
(DEPTCODE = 'PROD') OR
(DEPTCODE = 'WIRE') OR
(DEPTCODE = 'SLID') OR
(DEPTCODE = 'TOOL') OR
(DEPTCODE = 'CNCT')

 

[KenHigg]

SELECT REF, INITIALS + ' ' + SURNAME AS Employee, DEPTCODE, LEAVEDATE
FROM dbo.vwAllEmps
WHERE (LEAVEDATE IS NULL) AND ((DEPTCODE = 'CNCM') OR
(DEPTCODE = 'PROD') OR
(DEPTCODE = 'WIRE') OR
(DEPTCODE = 'SLID') OR
(DEPTCODE = 'TOOL') OR
(DEPTCODE = 'CNCT'))


???
ken

 

[Mile-O]

SELECT REF, INITIALS + " " + SURNAME AS Employee, DEPTCODE, LEAVEDATE
FROM dbo.vwAllEmps
WHERE LEAVEDATE IS NULL AND DEPTCODE In ("CNCM", "PROD", "WIRE", "SLID", "TOOL", "CNCT");

 

[livvie]

Thanks works fine with the extra brackets

 

[KenHigg]

SELECT REF, INITIALS + " " + SURNAME AS Employee, DEPTCODE, LEAVEDATE
FROM dbo.vwAllEmps
WHERE LEAVEDATE IS NULL AND DEPTCODE In ("CNCM", "PROD", "WIRE", "SLID", "TOOL", "CNCT");

Is there something like this 'In' statement in vba?

ken

 

[Mile-O]

Is there something like this 'In' statement in vba?

SELECT CASE is the nearest.

i.e.

Select Case DEPTCODE 
    Case Is = "CNCM", "PROD", "WIRE", "SLID", "TOOL", "CNCT"
        ' do something
    Case Else
        ' do soemthing else
End Select

 

[KenHigg]

Cool - Thanks