SQL Statement using WHERE with CASE and CHECKBOX

[jasonm]

I have a sql statement in which I am using the CASE expression in the WHERE clause based on whether a checkbox is selected or not.

Basically if the checkbox (Check59) is selected, I want the WHERE clause to include Security Level 3 (If the checkbox is NOT selected, only Security Level 1 and 2 shoudl be included.

Here is my code:



SELECT DISTINCTROW BEmployees.EmployeeCRN AS Expr1, BEmployees.LastName AS Expr2, BEmployees.FirstName AS Expr3, [FirstName] & " " & [LastName] AS Consultant
FROM BEmployees INNER JOIN [Name Substitution] ON BEmployees.FirstName = [Name Substitution].NameToShow
WHERE
(([Name Substitution].Current_User)=CurrentUser()) AND
CASE WHEN Me.Check59=1 Then (((BEmployees.SecurityLevel)=1 Or (BEmployees.SecurityLevel)=2 Or (BEmployees.SecurityLevel)=3)
ELSE (((BEmployees.SecurityLevel)=1 Or (BEmployees.SecurityLevel)=2)
END
ORDER BY BEmployees.LastName, BEmployees.FirstName DESC , [FirstName] & " " & [LastName];

BTW I am a newbie.
THX

[June7]

What is the issue? What happens when you open that query?

[jasonm]

The error is:

Syntax error (missing operator) in query expression..........and it proceeds to list the code between the WHERE and END??

[June7]

AFAIK, CASE does not work in Access.

[jasonm]

??? I have other CASE situations in other places in the DB???

[June7]

In other queries?

Select Case is valid structure in VBA but I don't think Access query engine can handle it.

[jasonm]

I think you are right - sorry I am trying to get up to speed.

I have this in a Row Source of a Form.

Is there any other way I can accomplish this?

[June7]

Do you mean Record Source?

I don't use dynamic parameterized queries. Review: http://www.allenbrowne.com/ser-62.html

[CJ_London]

use switch rather than case in sql, beats a load of nested iif's, however for here an iif will do (assuming using access backend)

SELECT DISTINCTROW BEmployees.EmployeeCRN AS Expr1, BEmployees.LastName AS Expr2, BEmployees.FirstName AS Expr3, [FirstName] & " " & [LastName] AS Consultant
 FROM BEmployees INNER JOIN [Name Substitution] ON BEmployees.FirstName = [Name Substitution].NameToShow
 WHERE
 (([Name Substitution].Current_User)=CurrentUser()) AND 
iif(Me.Check59=true,BEmployees.SecurityLevel=1 Or BEmployees.SecurityLevel=2 Or BEmployees.SecurityLevel=3, BEmployees.SecurityLevel=1 Or BEmployees.SecurityLevel=2)=True
ORDER BY BEmployees.LastName, BEmployees.FirstName DESC , [FirstName] & " " & [LastName];

No comment on
whether you are referencing Check59 correctly
why you are aliasing what looks like perfectly acceptable names with Expr1 etc
why you are ordering desc then asc