Syntax Error issues around Case When

Question

I am trying to write the following query for Microsoft Access and am encountering issues around the last three characters here, Can someone tell me what's wrong with my syntax for Access purposes:

SELECT DISTINCT Searches.Search ,IIF(Chemical_Names.Category IS NOT NULL, Chemical_Names.Category, IIF(CAS_Numbers.Category IS NOT NULL , CAS_Numbers.Category, IIF(Brand_Name.Category IS NOT NULL, Brand_Name.Category, IIF(PART.Category IS NOT NULL , PART.Category,CAT.Category)))) AS Category FROM Searches LEFT JOIN (SELECT * FROM Cat1 UNION SELECT * FROM Cat2 UNION SELECT * FROM Cat3 UNION SELECT * FROM Cat4) as CAT ON Searches.Search = CAT.Search LEFT JOIN (SELECT * FROM Part1 UNION SELECT * FROM Part2 UNION SELECT * FROM Part3 UNION
SELECT * FROM Part4) as PART ON Searches.Search = PART.Search LEFT JOIN Supplier_Name ON Searches.Search = Supplier_Name.Search LEFT JOIN Chemical_Names ON Searches.Search = Chemical_Names.Search LEFT JOIN Chemical_Names ON Searches.Search = CAS_Number.Search LEFT JOIN Brand_Name ON Searches.Search = Brand_Name.Search;

I corrected the tag to ms-access. access is as clear as mud. — Gregory Higley
There is no CASE WHEN in your code, and SWITCH isn't available in any version of Access SQL I've ever seen. Are you looking for IIF() (or IFF() - it's been a long time since I wrote any Access SQL) instead? — Ken White
Try using IIF(situation, do-this-when-true, do-this-when-false) instead of switch. See stackoverflow.com/questions/9310098/… for an example — zedfoxus

Tim Biegeleisen Tim Biegeleisen · Accepted Answer · 2017-06-08T02:44:27

I think you can try using the Nz() function to conditionally show the first non null category in your chain of preference:

SELECT DISTINCT
    Nz(Chemical_Names.Category,
        Nz(CAS_Number.Category,
            Nz(Brand_Name.Category,
                Nz(PART.Category, CAT.Category)))) AS Category
FROM Searches
...

The Nz() function is Access' version of what would be COALESCE() in most other databases.

You could also handle this use IIF(), which is Access' version of the CASE expression:

SELECT DISTINCT
    IIF(Chemical_Names.Category IS NOT NULL, Chemical_Names.Category,
        IIF(CAS_Number.Category IS NOT NULL, CAS_Number.Category,
            IIF(Brand_Name.Category IS NOT NULL, Brand_Name.Category,
                IIF(PART.Category IS NOT NULL, PART.Category, CAT.Category)))) AS Category
FROM Searches
...

Syntax Error issues around Case When

2 Answers