Copying a query from SQL server to MS Acess

Question

I've written a query in SQL server that runs okay.

I need to run this query in Access as there is more data I need to pull into the query.

However when I copy the SQL into Access it doesn't work, I get a syntax error, Missing operator in query expression.

SELECT main.Tbl_ServiceOrder.ServiceOrder
, main.Tbl_Serviceorder.BusinessPartnerNumber
, main.Tbl_ServiceOrder.ExternalPointofDeliverynumber
, main.Tbl_ServiceOrder.ServiceProduct
, main.Tbl_ServiceOrder.SOCreatedOn
, main.Tbl_ServiceOrder.MainUserStatus
, main.Tbl_ServiceOrder.MainUserStatusDesc
, main.Tbl_ServiceOrder.ReasonCode
, main.Tbl_ServiceOrder.ReasonCodeDesc
, main.Tbl_ServiceOrder.SOActualFinishDate
, main.Tbl_ServiceOrder.BasicStartDate
,CAST(CASE WHEN reasoncode <> 'CMPL'  THEN 'Pre Install' 
WHEN reasoncode = 'CMPL' and SOActualFinishDate < (getdate()-182) THEN 'SMART BAU'
ELSE 'Post Install' END as Varchar) as SMRTPot
FROM main.Tbl_ServiceOrder
WHERE main.Tbl_ServiceOrder.ServiceProduct ='SMINSTALL' 
OR main.Tbl_ServiceOrder.ServiceProduct ='SMEXCHANGE'

The WHEN on the first line of the CAST is highlighted by access as the error point.

What am i doing wrong?

I've tried changing the cast to the IIF below

, IIF (reasoncode <> 'CMPL','Pre Install',(IIF SOActualFinishDate < (getdate()-182),'SMART BAU','Post Install')) as SMRTPot

Above gives syntax error (comma) in query expression

 IIF (reasoncode <> 'CMPL','Pre Install',(IIF SOActualFinishDate < (getdate()-182),'SMART BAU','Post Install')) as SMRTPot

above gives syntax error in query expression.

Okay so this is what I'm using now:

SELECT main.Tbl_ServiceOrder.ServiceOrder
, main.Tbl_Serviceorder.BusinessPartnerNumber
, main.Tbl_ServiceOrder.ExternalPointofDeliverynumber
, main.Tbl_ServiceOrder.ServiceProduct
, main.Tbl_ServiceOrder.SOCreatedOn
, main.Tbl_ServiceOrder.MainUserStatus
, main.Tbl_ServiceOrder.MainUserStatusDesc
, main.Tbl_ServiceOrder.ReasonCode
, main.Tbl_ServiceOrder.ReasonCodeDesc
, main.Tbl_ServiceOrder.SOActualFinishDate
, main.Tbl_ServiceOrder.BasicStartDate
,Switch(reasoncode <> 'CMPL', 'Pre Install',
        reasoncode = 'CMPL' AND SOActualFinishDate < ( Getdate() - 182 ), 'SMART BAU', 
        True, 'Post Install') as SMRTPot
FROM main.Tbl_ServiceOrder
WHERE main.Tbl_ServiceOrder.ServiceProduct ='SMINSTALL' OR main.Tbl_ServiceOrder.ServiceProduct ='SMEXCHANGE'

Error message is now could not find file 'file path\main.mdb

http://stackoverflow.com/questions/14920116/does-ms-access-support-case-when-clause-if-connect-with-odbc — Mattia Nocerino, Oct 17 '16 at 09:54
have you looked up the CASE syntax for Access and compared it with yours ? — GuidoG, Oct 17 '16 at 09:54

score 1 · Answer 1 · answered Oct 17 '16 at 10:07

Too many issues and too long for a comment.

You are using SQL Server syntax and not MS Access SQL syntax.

WHEN...CASE is not valid in MS ACcess SQL, you should use IIF or SWITCH instead

CAST is also not valid. You can convert to string using CStr

GetDate is also not valid, you should use DATE().

If you want to substract 182 days then you should do DATEADD('d', DATE() , -182)

But the most handy way to handle this is probably to make passthrough query that will be executed on SQL Server and not MS Access (thus not on linked tables), so you can keep your SQL Server syntax. Search that path.

Pass through is what I was looking for --Thanks – r.baton Oct 17 '16 at 10:15 — r.baton, Oct 17 '16 at 10:15

Copying a query from SQL server to MS Acess

1 Answers1