CODEWITHSUNDEEP
sqlms-access
VT555
VT555

Reputation: 91

SELECT CASE with LIKE statement sql

I want to use SQL to query on some data I have in my access (2010) database.

What I would like to do is pull out all the people with IBS. (This I know how to do). Then using SQL create a variable that stores 0 if there is "?IBS" (not diagnosed) or stores 1 if there is "IBS" stored (diagnosed IBS) within this variable. This information is stored in a text variable called "AdditionalHealthProblems".

I'm trying to use a SELECT CASE statement with Like but cannot seem to get it to work.

SELECT AdditionalHealthProblems, 'Diagnosed' = 
       CASE 
           WHEN AdditionalHealthProblems LIKE '*?IBS*' THEN '0'
           ELSE '1'
       END
FROM tblFollowUpQs
WHERE (((tblFollowUpQs.AdditionalHealthProblems) Like '*IBS*'));

But this is not working. I'm getting an error in the "'Diagnosed' = CASE WHEN AdditionalHealthProblems LIKE '*?IBS*' THEN '0' ELSE '1'END" statement via an error message.

Can someone please tell me what is wrong? Or if there is a better way to get this information?

Thank you!

-----------------EDIT--------------------

Changed to 

SELECT tblFollowUpQs.AdditionalHealthProblems, CASE 
WHEN tblFollowUpQs.AdditionalHealthProblems LIKE 'IBS' THEN '0' END As Diagnosed
FROM tblFollowUpQs
WHERE (((tblFollowUpQs.AdditionalHealthProblems) Like 'IBS'));

But its still not working...

The error message give a syntax error and highlights WHEN

---------------ANSWER--------------------------

Switched the select case to Switch() function and it worked!

Here's the final code:

SELECT tblFollowUpQs.AdditionalHealthProblems, Switch( tblFollowUpQs.AdditionalHealthProblems LIKE '*[?]*', 1, tblFollowUpQs.AdditionalHealthProblems NOT LIKE '*[?]*', 0)
FROM tblFollowUpQs
WHERE (((tblFollowUpQs.AdditionalHealthProblems) Like '*IBS*'));

Upvotes: 0

Views: 6115

Answers (2)

Banu Priya
Banu Priya

Reputation: 61

Query

SELECT AdditionalHealthProblems, Diagnosed= (
   CASE 
       WHEN AdditionalHealthProblems LIKE '?IBS' THEN '0'
       ELSE '1'
   END)
FROM tblFollowUpQs
WHERE (((tblFollowUpQs.AdditionalHealthProblems) Like '%IBS'));

Upvotes: 0

jpw
jpw

Reputation: 44881

Microsoft Access does not support the CASE...WHEN statement. Use the IIF statement instead:

IIF(expression, value if true, value if false)

reference

SELECT tblFollowUpQs.AdditionalHealthProblems, 
IIf(tblFollowUpQs.AdditionalHealthProblems Like "IBS",1,0) AS [Diagnosed] 
FROM tblFollowUpQs
WHERE (((tblFollowUpQs.AdditionalHealthProblems) Like "'IBS"));

If you need to use wildcard characters you can read more on office.microsoft.com

Upvotes: 5

Related Questions