title: SELECT CASE with LIKE statement sql
body: <p>I want to use SQL to query on some data I have in my access (2010) database.</p>
  
  <p>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".</p>
  
  <p>I'm trying to use a SELECT CASE statement with Like but cannot seem to get it to work.</p>
  
  <pre><code>SELECT AdditionalHealthProblems, 'Diagnosed' = 
         CASE 
             WHEN AdditionalHealthProblems LIKE '*?IBS*' THEN '0'
             ELSE '1'
         END
  FROM tblFollowUpQs
  WHERE (((tblFollowUpQs.AdditionalHealthProblems) Like '*IBS*'));
  </code></pre>
  
  <p>But this is not working. I'm getting an error in the "<code>'Diagnosed' = CASE WHEN AdditionalHealthProblems LIKE '*?IBS*' THEN '0' ELSE '1'END"</code> statement via an error message.</p>
  
  <p>Can someone please tell me what is wrong?
  Or if there is a better way to get this information?</p>
  
  <p>Thank you!</p>
  
  <p><em>-----------------EDIT--------------------</em></p>
  
  <p>Changed to </p>
  
  <pre><code>SELECT tblFollowUpQs.AdditionalHealthProblems, CASE 
  WHEN tblFollowUpQs.AdditionalHealthProblems LIKE 'IBS' THEN '0' END As Diagnosed
  FROM tblFollowUpQs
  WHERE (((tblFollowUpQs.AdditionalHealthProblems) Like 'IBS'));
  </code></pre>
  
  <p>But its still not working...</p>
  
  <p>The error message give a syntax error and highlights WHEN</p>
  
  <p>---------------ANSWER--------------------------</p>
  
  <p>Switched the select case to Switch() function and it worked!</p>
  
  <p>Here's the final code:</p>
  
  <pre><code>SELECT tblFollowUpQs.AdditionalHealthProblems, Switch( tblFollowUpQs.AdditionalHealthProblems LIKE '*[?]*', 1, tblFollowUpQs.AdditionalHealthProblems NOT LIKE '*[?]*', 0)
  FROM tblFollowUpQs
  WHERE (((tblFollowUpQs.AdditionalHealthProblems) Like '*IBS*'));
  </code></pre>