Transferring a SQL Server Query to Microsoft Access

Data and Databases
1

Attempting to port a SQL Server query to Access. SQL’s CASE expression works there, but Access returns syntax errors. Instead, using Switch:

SELECT a.OrderID, a.ClientID, a.DeliveryID,
       a.Item, a.OrderDate,
       Switch(a.Code <> 'COMP', 'Pre Setup',
              a.Code = 'COMP' AND a.CompletionDate < (Date()-180), 'Delayed',
              True, 'Post Setup') AS ProcessStatus
FROM OrdersTable a
WHERE a.Item IN ('Type1','Type2');

Why do these errors occur?

2

Syntax errors in Access often originate from differences in how expressions are handled between SQL Server and Access SQL. My experience indicates that Access does not support SQL Server expressions like CASE, and the Switch function requires a careful balance of conditions and corresponding values. In addition, functions such as Date() may behave differently, necessitating adjustments in the query. Ensuring that boolean logic is correctly expressed and that field references are valid in Access can resolve these issues. A detailed review of each clause in Access syntax typically leads to a successful conversion.

3

hey, maybe try enlosing some conditions in brackets; access can be fussy with logic grouping. also, double-check that your date fn works as expected. sometimes even minor deviation in syntax like this can throw errors