Writing a SQL query in VBA using IFF statement, Get missing expression error
I've been trying to figure this out for days and have had no luck. I am trying to write a SQL string command inside VBA.
I get the message "ORA-00936: error while calculating the error"
strSql4 = "SELECT FWLOTID, ENDDATETIME, ENDCODE, DRAW, IIF([ENDCODE]=""6"", ""MSNAG"",""0"") " ' Or ENDCODE='10' Or ENDCODE='11' Or ENDCODE='12' Or ENDCODE='14'"
strSql3 = "FROM EVENTS.ODSWIND WHERE ENDDATETIME > to_date('" & Format(dtEndDate, "MM/dd/yyyy") & "','MM/DD/YYYY') "
On Error Resume Next
rsPRC.Close
On Error GoTo ErrorHandler
rsPRC.CursorLocation = adUseClient
rsPRC.CursorType = adOpenStatic
rsPRC.LockType = adLockOptimistic
rsPRC.Open strSql4 & strSql3, cnPRC
+3
source to share
2 answers
Check the data type for [ENDCODE]. If it's a text box, try this:
strSql4 = "SELECT FWLOTID, ENDDATETIME, ENDCODE, DRAW, IIF([EVENTS].[ODSWIND].[ENDCODE]=""6"", ""MSNAG"",""0"") as IsItSix "
If [ENDCODE] is numeric, try the following:
strSql4 = "SELECT FWLOTID, ENDDATETIME, ENDCODE, DRAW, IIF([EVENTS].[ODSWIND].[ENDCODE]=6, ""MSNAG"",""0"") as IsItSix "
Also let us know which database you are submitting this code to. Is this an Access Jet database? Access Ace Database? SQL Server database? Any additional information you give will enable us to help you.
EDIT
Also ... IIF will only work with Access Sql query.
Your error looks like an Oracle error.
Assuming you are shipping Oracle try this:
strSql4 = "SELECT FWLOTID, ENDDATETIME, ENDCODE, DRAW, CASE WHEN [ENDCODE]= '6' THEN 'MSNAG' ELSE '0' END as IsItSix "
EDIT AGAIN
Try this: (If that doesn't work, please give me the exact error message.)
strSql4 = "SELECT FWLOTID, ENDDATETIME, ENDCODE, DRAW, CASE ENDCODE WHEN '6' THEN 'MSNAG' ELSE '0' END AS ISITSIX "
+1
source to share