Syntax error (missing operator) in query expression Access database

Question

Receive an error for the following. I stripped it all out and still receive the same error when I run the following:

SELECT dbo_CUS_SortRef , dbo_IMA_ItemID
CASE when dbo_SOI_IMA_RecordID is null then dbo_SOI_MiscLineDescription else dbo_IMA.IMA_ItemName end [dbo_IMA_ItemName], dbo_SOD_RequiredQty, dbo_SOD_UnitPrice , Round(([dbo_SOD_RequiredQty]*[dbo_SOD_UnitPrice]*(1-[dbo_SOD_DiscPercent])+[dbo_SOD_DelExtChgsAmt]),2) [ExtendedAmount], dbo_SOM_SalesOrderID , dbo_SOD_RequiredDate , dbo_SOD_PromiseDate , isnull(qrySODetail_sub.SumSOSShipQty,0) [dbo_SumSOSShipQty], (isnull(dbo_SOD_RequiredQty,0) - isnull(dbo_SumSOSShipQty,0)) [BalanceDueQty], Round(((isnull(dbo_SOD_RequiredQty,0) - isnull(dbo_SumSOSShipQty,0))*[dbo_SOD_UnitPrice]*(1-[dbo_SOD_DiscPercent])+[dbo_SOD_DelExtChgsAmt]),2) [BalanceDueAmount],dbo_SOI_SOLineNbr  
FROM dbo_Customer RIGHT JOIN dbo_SalesOrder ON dbo_Customer.CUS_RecordID = dbo_SalesOrder.SOM_CUS_RecordID 
INNER JOIN dbo_SalesOrderLine ON SalesOrder.SOM_RecordID = SalesOrderLine.SOI_SOM_RecordID 
INNER JOIN dbo_SalesOrderDelivery ON SalesOrderLine.SOI_RecordID = SalesOrderDelivery.SOD_SOI_RecordID
LEFT JOIN IMA ON dbo_SalesOrderLine.SOI_IMA_RecordID = IMA.IMA_RecordID 
LEFT JOIN MMT_RMAWarrantyCode AS ExtWarranty ON dbo_SOD_ExtdWarrantyCodeID = ExtWarranty.MRWC_WarrantyCode 
LEFT JOIN MMT_RMAWarrantyCode AS Warranty ON dbo_SOD_WarrantyCodeID = dbo_Warranty.MRWC_WarrantyCode 
LEFT JOIN RMA_Replacement ON dbo_SOD_RecordID = dbo_RPL_PositiveReplacementDelivery_RecordID 
LEFT JOIN RMA_Line ON dbo_RPL_RMAL_RecordID = dbo_RMAL_RecordID 
LEFT JOIN RMA_Header ON dbo_RMAL_RMA_RecordID = dbo_RMA_RecordID 
INNER JOIN (SELECT dbo_SalesOrderDelivery.SOD_SOM_RecordID, dbo_SalesOrderDelivery.SOD_SOLineNbr, dbo_SalesOrderDelivery.SOD_RecordID,Sum(dbo_ShipmentLine.SHL_ShipQty) AS SumSOSShipQty, Sum(dbo_SalesOrderDelivery.SOD_RequiredQty) As SumOfSOD_RequiredQty FROM dbo_SalesOrderDelivery 
LEFT JOIN ShipmentLine ON dbo_SalesOrderDelivery.SOD_RecordID = dbo_ShipmentLine.SHL_SOD_RecordID 

GROUP BY dbo_SalesOrderDelivery.SOD_SOM_RecordID, dbo_SalesOrderDelivery.SOD_SOLineNbr, dbo_SalesOrderDelivery.SOD_RecordID) as qrySODetail_sub ON SalesOrderDelivery.SOD_RecordID = qrySODetail_sub.SOD_RecordID Where dbo_SOD_ShipComplete = 0 AND dbo_SOI_CancelledFlag = 0  And dbo_SOM_IVM_InvoiceID is null And not dbo_CUS_CustomerID in ('C16136', 'C100553', 'C13057', 'C100198', 'C4010', 'C100062');

you need a comma at end of first line `SELECT dbo_CUS_SortRef , dbo_IMA_ItemID ,` — codeulike, Apr 25 '22 at 14:52
Right, `CASE` not supported in Access SQL. And [IsNull](https://support.microsoft.com/en-us/office/isnull-function-f963233b-1c1e-4b0c-8bc3-3e8c0ea67c61) accepts only one parameter. — HansUp, Apr 25 '22 at 14:58
[Access requires parentheses in the FROM clause for queries which include more than one join.](https://stackoverflow.com/a/20929533/77335) — HansUp, Apr 25 '22 at 14:59
I have incorporated your comments into my messy answer and now I feel a bit icky, apologies HansUp and Nathan_Sav — codeulike, Apr 25 '22 at 15:03
Please clarify your specific problem or provide additional details to highlight exactly what you need. As it's currently written, it's hard to tell exactly what you're asking. — Community, Apr 26 '22 at 03:42
Are you trying to run this SQL in an Access query object? It is not using Access Jet SQL so you should be running this as a pass-through query. — Ben, Apr 26 '22 at 03:44
there are too many errors in this query to fix it blindly (ie without understanding the table structures and data). First review all table names (take IMA, for example, sometimes you refer to it as IMA, sometimes as dbo_IMA), sometimes the column names seem to have dbo_ prefix; this seems wrong, becuase it would have been table names only (as in `dbo_SOD_WarrantyCodeID = dbo_Warranty.MRWC_WarrantyCode`). Correct all those; replace isnull with NZ, replace CASE WHEN with IIF. Start with a few joins to get the syntax right, and build from there. — tinazmu, Apr 26 '22 at 06:07
Reading @Ben's comments: yes, you can do it as a pass-through query; then you wouldn't have to translate anything (use the original query). — tinazmu, Apr 26 '22 at 06:11

codeulike · Answer 1 · 2022-04-25T15:02:31.253

1

you need a comma at end of first line:

SELECT dbo_CUS_SortRef , dbo_IMA_ItemID ,

edit: oh also you cant use CASE WHEN THEN ELSE END in Access, thats a T-SQL thing. You could try switch instead https://stackoverflow.com/a/54906589/22194

edit: oh and isnull only has one parameter in Access

edit: oh and Access requires parentheses in the FROM clause for queries which include more than one join.

with thanks to HansUp, Nathan_Sav in the comments

edited Apr 25 '22 at 15:02

answered Apr 25 '22 at 14:55

codeulike

22,514
29
120
167

Thank you all for the assistance. I'm attempting to write it with the parentheses joins but I'm having issues because there are so many inner joins. Can someone post what the whole statement should be? – Jordan Pizarro Apr 25 '22 at 16:00

Syntax error (missing operator) in query expression Access database

1 Answers1