Syntax error (missing operator) in query expression 1

[cumap]

Hello,

I had this query works for MS SQL, but getting error running it for Access DB.

"SELECT pgRMAprods.prodSerialNo, " _
& "	SUM(CASE WHEN pgProducts.prodType='DVR' THEN pgRMAprods.itemQty END) AS countDVR, " _
& "	pgProducts.prodDesc " _
& "FROM pgRMAprods " _
& "INNER JOIN pgProducts " _
& "	ON pgRMAprods.idProduct = pgProducts.idProduct " _
& "WHERE pgRMAprods.idRMA = " & validSQL(rmaID,"I") & " " _
& "GROUP BY pgRMAprods.prodSerialNo, pgProducts.prodDesc " _
& "ORDER BY pgProducts.prodType DESC"

The error output is:
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'SUM(CASE WHEN pgProducts.prodType='DVR' THEN pgRMAprods.itemQty END)'.


I guessed it can be query different between MS SQL and Access but don't know what is, please help.

 

[markros]

In Access you need to use IIF function for conditional logic, e.g.
SUM(IIF(pgProducts.ProdType = 'DVR', pgRMAprods.ItemQty,0)) as CountDVR.

I hope this is the only one change required, although Access also likes to have lots of () for the JOIN conditions.

PluralSight Learning Library

 

[cumap]

Thank you markros for your reply. The error has gone way, but result is not as I expected (due to my fault of setting up the query). I will need to have another query to get the right result, and will come back here later if anymore help is acquired.

Thanks again.