CODEWITHSUNDEEP
ms-access
A.Goutam
A.Goutam

Reputation: 3494

case statement in MS Access query give error

I am using below query in Ms Access. And this gives me error Syntax error in your query expression CASE WHEN not ... . Can you please tell me what I am doing wrong? In Sql Server 2008 R2, the case statement runs correctly.

SELECT TableApron.RadButtonNo, TableApron.ShortName, QueryForNot1.InspectionDate, QueryForNot1.Findings, QueryForNot1.Status, QueryForNot1.Initials, TableApron.DeptName, TableApron.Lost, TableApron.InServelDate, TableApron.RemovedDate, TableApron.PrivateUserName, TableApron.PrivateUserEmail, TableApron.ApronType, TableApron.Manufacturer
FROM TableApron LEFT JOIN QueryForNot1 ON TableApron.RadButtonNo=QueryForNot1.RadButtonNoI
WHERE (((TableApron.Lost)="N" Or (TableApron.Lost)=[@Lost]) And ((TableApron.InServelDate) Is Null Or (TableApron.InServelDate) Between CDATE([@From]) And CDATE([@To]) Or (TableApron.InServelDate)<CDATE([@To])) And ((TableApron.RemovedDate) Is Null Or (TableApron.RemovedDate) Between CDATE([@From]) And CDATE([@To]) Or (TableApron.RemovedDate)>CDATE([@To])))
ORDER BY
CASE
       WHEN not TableApron.RadButtonNo like '%[^0-9]%' THEN CONVERT(int,TableApron.RadButtonNo)
       WHEN TableApron.RadButtonNo like '[0-9]%' THEN CONVERT(int,SUBSTRING(TableApron.RadButtonNo,1,PATINDEX('%[A-Z]%',TableApron.RadButtonNo)-1))
    END,
    CASE
       WHEN not TableApron.RadButtonNo like '%[^0-9]%' THEN NULL
       WHEN TableApron.RadButtonNo like '[0-9]%' THEN SUBSTRING(TableApron.RadButtonNo,PATINDEX('%[A-Z]%',TableApron.RadButtonNo),9000)
       ELSE TableApron.RadButtonNo
    END;

Upvotes: 0

Views: 102

Answers (1)

HansUp
HansUp

Reputation: 97101

The CASE statement triggers the first reported error because it is not supported in Access SQL. Use IIf() instead as @Gustav suggested.

However then you will encounter additional errors because CONVERT, SUBSTRING, and PATINDEX are also not supported in Access SQL.

Instead of CONVERT, use CInt() to cast a value to Access Integer or CLng() for Long Integer. Or you could use Val() and let Access decide which numeric datatype to give you.

Instead of SUBSTRING, use Mid().

Instead of PATINDEX, use InStr().

Assuming those suggestions eliminate the syntax errors, you may still have an issue with the Like wildcard.

If you will be running the query from the query designer or elsewhere under DAO, Access expects * instead of % as the wildcard. % is the correct wild card only when the query is run from ADO/OleDb.

Upvotes: 1

Related Questions