Using IIF Statement In SQL String (To Oracle Database Via ODBC)

[Bcanfield83]

Hi,
I'm attempting to query an Oracle Database through a VBA module in my Access DB. Within the query, I'm trying to use CASE WHEN..THEN..ELSE to evaluate a particular column.
If I login to this Oracle Database using TOAD, the "CASE WHEN.." syntax shown below works perfectly.

SELECT SG_DESCRIPTION, CON_DESCRIPTION, 
CASE WHEN ASI_AA_LEVEL = 'E' THEN 'Enabled' ELSE 'Disabled/Nd Open Clt' END as SCREEN_STATUS
FROM MYTABLE

However, I'm having trouble translating that into my VBA module. I keep receiving a "Missing Expression" error when trying to run the querydef, so I must be missing something in my syntax but can't seem to figure out where I'm going wrong, e.g.:

strSQL = "SELECT SG_DESCRIPTION, CON_DESCRIPTION," & _
" CASE WHEN ASI_AA_LEVEL = 'E' THEN 'Enabled' ELSE 'Disabled/Nd Open Clt' END as SCREEN_STATUS," & _
" FROM MYTABLE"

Within the VBA module, I have the SQL Query defined in a string variable and then creating a querydef with a connection to an Oracle Database (the connection string is defined under strConnect)

Dim db As DAO.Database
Dim qdef As DAO.QueryDef
Dim strSQL As String
Dim strConnect As String

strConnect = "<Connection String for ODBC>"

Set db = CurrentDb

Set qdef = db.CreateQueryDef("MyQuery")
 
With qdef
 
        .Connect = strConnect
        .SQL = strSQL
        .ReturnsRecords = True
       
End With

[pbaldy]

You've got a stray comma after the last field:

" CASE WHEN ASI_AA_LEVEL = 'E' THEN 'Enabled' ELSE 'Disabled/Nd Open Clt' END as SCREEN_STATUS," & _

[Bcanfield83]

You've got a stray comma after the last field:

" CASE WHEN ASI_AA_LEVEL = 'E' THEN 'Enabled' ELSE 'Disabled/Nd Open Clt' END as SCREEN_STATUS," & _

Thanks, that was actually a syntax error from me attempting to copy and paste the string into my post - oops!
I did figure this out though - for some reason, when I build the SQL string in Access, it only works if I enclose the "CASE WHEN..ELSE..END" portion in parenthesis like so:

(CASE WHEN ASI_AA_LEVEL = 'E' THEN 'Enabled' ELSE 'Disabled/Nd Open Clt' END) as ACCESS_LEVEL

What threw me off is that it works fine without the parenthesis in TOAD for Oracle:

CASE WHEN ASI_AA_LEVEL = 'E' THEN 'Enabled' ELSE 'Disabled/Nd Open Clt' END as ACCESS_LEVEL

[pbaldy]

Glad you got it sorted. I'm a bit surprised, as pass through queries are executed on the server, so the same syntax should work. That said, I have no Oracle experience, only SQL Server.