CODEWITHSUNDEEP
sql-serverms-accessvbacaseiif
xJustin8
xJustin8

Reputation: 11

Convert CASE to IIF for an Access query

I can not seem to find how to convert the below query that is written in T-SQL into an access query to be used in vba. I've read that using IIF instead of CASE (since case isn't supported by Access' SQL), but tried many different ways and cannot get it to work.

sSQL = "SELECT ba.BusinessEntityID,ba.AddressID
          ,(CASE WHEN ba.BusinessEntityID > 5
            THEN
            (SELECT pp.FirstName FROM [Person].[Person] pp
            WHERE pp.BusinessEntityID = ba.BusinessEntityID)
           Else 'AA' END) AS 'TEST'
    FROM Person.BusinessEntityAddress ba
    INNER JOIN person.AddressType at ON at.AddressTypeID = ba.AddressTypeID
    WHERE ba.BusinessEntityID < 11;"`

I get the error message as follows when trying to run the query above..

Run-Time Error 3075 Syntax Error (missing operator) in query expression '(CASE WHEN ba.BusinessEntityID > 5 THEN (SELECT pp.FirstName FROM [Person].[Person] pp WHERE pp.BusinessEntityID = ba.BusinessEntityID) Else 'AA' END) AS 'TEST'

Upvotes: 1

Views: 295

Answers (2)

3per
3per

Reputation: 384

IIF syntax :

IIF(condition, if_true_part, if_false_part)

Also, you cannot make pseudonyms without "AS" in MS Access.

So, try next

sSQL = "SELECT ba.BusinessEntityID,ba.AddressID
          ,IIF(ba.BusinessEntityID > 5
            ,(SELECT pp.FirstName FROM Person pp
            WHERE pp.BusinessEntityID = ba.BusinessEntityID)
            ,'AA') AS 'TEST'
    FROM BusinessEntityAddress AS ba
    INNER JOIN AddressType AS at ON at.AddressTypeID = ba.AddressTypeID
    WHERE ba.BusinessEntityID < 11;"

There are 2 other ways:

1) To create a VBA Function, which will be return "FirstName";

2) JOIN (left or inner, what you need) the table "Person"

Upvotes: 0

area9
area9

Reputation: 391

When using Access, use the SWITCH function: http://www.techonthenet.com/access/functions/advanced/switch.php

The else part is just done by saying that the condition is true.

sSQL = "SELECT ba.BusinessEntityID, ba.AddressID, 
         SWITCH(ba.BusinessEntityID > 5, 
        (SELECT pp.FirstName FROM [Person].[Person] 
        WHERE pp.BusinessEntityID = ba.BusinessEntityID), 
        TRUE, 'AA') AS 'TEST'
        FROM Person.BusinessEntityAddress ba
        INNER JOIN person.AddressType at ON at.AddressTypeID = ba.AddressTypeID
        WHERE ba.BusinessEntityID < 11"

Upvotes: 0

Related Questions