[SOLVED] Case when in MS Access

Case when in MS Access

I am trying to identify some rows using IIF and Not IN operator but I get syntax error. I want to identify the rows where none of the column contain A1 and A2 using the below query. I don't get the desired results. Any help much appreciated and thank you in advance.

Table: (Note: There are upto 16 variables/Columns in original table)

ID  KH1 KH2 KH3 KH4 KH5 KH6

001 A2  B1  C1  D1  E1  F1
002 F1  A1  B1  D1  E1  G1
003 A1  B1  D1  E1  
004 XX  XX  XX  XX  XX  XX
005 AA  AB  AC  A2  XX
006 XX  XX  XX  A1  
007 XX  XX  XX  XX  A1  XX
008 XX  XX  AE  XX  XX  AC

Output:

ID  KH1 KH2 KH3 KH4 KH5 KH6 Identified

001 A2  B1  C1  D1  E1  F1  No
002 F1  A1  B1  D1  E1  G1  No
003 XX  B1  D1  E1          Yes     
005 AA  AB  AC  A2  XX      Yes
006 XX  XX  XX  A1          No
007 XX  XX  XX  XX  A1  XX  No

My Query

Select * from Table
IIF (((Nz([KH1],' ')) Not in ('A1', 'A2')) AND ((Nz([KH2],' ')) Not in ('A1', 'A2')) AND ((Nz([KH3],' ')) Not in ('A1', 'A2')) AND ((Nz([KH4],' ')) Not in ('A1', 'A2')) AND ((Nz([KH5],' ')) Not in ('A1', 'A2')) AND ((Nz([KH6],' ')) Not in ('A1', 'A2')));

Solution

The IIF() function in your code is missing the truepart and the elsepart:

SELECT *,
  IIF (    Nz([KH1], '') NOT IN ('A1', 'A2') 
       AND Nz([KH2], '') NOT IN ('A1', 'A2')
       AND Nz([KH3], '') NOT IN ('A1', 'A2')
       AND Nz([KH4], '') NOT IN ('A1', 'A2')
       AND Nz([KH5], '') NOT IN ('A1', 'A2')
       AND Nz([KH6], '') NOT IN ('A1', 'A2'),
       'Yes',
       'No'
  ) AS Identified
FROM tablename;

Or:

SELECT *,
  IIF (   Nz([KH1], '') IN ('A1', 'A2') 
       OR Nz([KH2], '') IN ('A1', 'A2')
       OR Nz([KH3], '') IN ('A1', 'A2')
       OR Nz([KH4], '') IN ('A1', 'A2')
       OR Nz([KH5], '') IN ('A1', 'A2')
       OR Nz([KH6], '') IN ('A1', 'A2'),
       'No',
       'Yes'
  ) AS Identified
FROM tablename;