IIF and Case is SQL through ASP
Posted on 2004-10-07
After reading multiple questions and answers in this forum I've tried a number of solutions to an error from SQL.
"Incorrect syntax near '>'."
Through changing operator, I've determined it points to the >= in
case tblARInvoices.date when >= '01/12/04'
Prior to using a case expression, I was using IIF but read that this is not supported. I changed to the CASE and got the same error message.
I'm missing something.
Any help is appreciated.
SQL = "SELECT tblARInvoices.[Invoice#], tblARInvoices.Date, " & _
"tblOEOrders.[Order#], tblCustomers.CustomerName, " & _
"tblInventory.[Part#], tblInventory.Description, " & _
"tblARInvoiceDetail.SplitAmount AS Sale, " & _
"case tblARInvoices.date when >= '01/12/04' And <='02/29/2004' then '20%' else '10%') end AS Pct, " & _
"case tblARInvoices.date when >= '01/12/04' And " & _
" <='2/29/2004') then [Sale]*0.2 else [sale]*0.1 end AS Accrual, " & _
" case accrual when >5000 then 5000 else accrual AS Earned " & _