SQL Error (Missing Operator) In query operation error

Question

I am building an application and using MS Access as database(Normally I don't use MS Access but the client needs service-less database). Anyways, I have multiple tables and using joins, I am trying to acquire output, here is the query

SELECT tblSalesDetail.Sales_Details_ID, tblProduct.Product_Name, 
       tblSalesDetail.Rate, tblSalesDetail.Quantity, tblSalesDetail.TaxableAmount, 
       tblSalesDetail.TaxableAmount + tblSalesDetail.CGST_Amt + 
       tblSalesDetail.SGST_Amt + tblSalesDetail.IGST_Amt AS TotalAmt 
FROM tblSalesMain 
INNER JOIN tblSalesDetail 
   ON tblSalesMain.Sales_Main_ID = tblSalesDetail.Sales_Main_ID 
INNER JOIN tblProduct 
   ON tblSalesDetail.Product_ID = tblProduct.Product_ID 
WHERE tblSalesMain.Sales_Main_ID=1

This query works perfectly in SQL Server but having the following error when trying to run in MS Access with text selected at _Name at tblProduct.Product_Name in the first line.

I tried by changing or removing that column, but it's not working.

I tried StackOverflow answers posted but none of them worked by the way. So it's not like I am directly posting a question here without trying to solve this.

I know this might be a simple problem but I am stuck. Let me know workaround this.

I know this is off the above topic but can anyone suggest me good service-less, localdb (not mdf based SQL Db) for visual studio?

Regards

@GordonLinoff Well, suggesting a good one will solve above I hope. Anyone good DB you can suggest? — Mahadev, May 04 '19 at 13:24
@Andre Looks like it. It's bad I couldn't find that earlier. Thanks for notifying though. — Mahadev, May 04 '19 at 13:36

score 2 · Accepted Answer · answered May 04 '19 at 13:24

2

MS Access is quite finicky about syntax. Try this:

SELECT tblSalesDetail.Sales_Details_ID, tblProduct.Product_Name, tblSalesDetail.Rate, 
       tblSalesDetail.Quantity, tblSalesDetail.TaxableAmount,
       (tblSalesDetail.TaxableAmount + tblSalesDetail.CGST_Amt + tblSalesDetail.SGST_Amt + tblSalesDetail.IGST_Amt) AS TotalAmt
FROM (tblSalesMain INNER JOIN
      tblSalesDetail
      ON tblSalesMain.Sales_Main_ID = tblSalesDetail.Sales_Main_ID
     ) INNER JOIN
     tblProduct
     ON tblSalesDetail.Product_ID = tblProduct.Product_ID WHERE tblSalesMain.Sales_Main_ID = 1

answered May 04 '19 at 13:24

Gordon Linoff

1,242,037
58
646
786

Thanks. This worked but what was the problem and what have you changed. I know I can compare both queries but asking for the sake of explanation and other developers. Please elaborate on the answer – Mahadev May 04 '19 at 13:26
2

@Mahadev ... See above linked duplicate. MS Access SQL requires parentheses pairing for queries with more than one `JOIN`. – Parfait May 04 '19 at 13:51
@GordonLinoff, about that *finicky* syntax, please see, share, vote, or comment on my recent [ticket](https://access.uservoice.com/forums/319956-access-desktop-application/suggestions/37193662-upgrade-access-sql-dialect-to-current-ansi-standar) to the Access team to upgrade its dialect to current standards of which includes this parenthesis requirement that frustrate new users. – Parfait May 04 '19 at 13:56
@Parfait: Got it. And voted on your ticket. – Mahadev May 04 '19 at 14:19
@Parfait . . . Fortunately, there are many choices of database out there that *do* support more standard SQL syntax and functionality. – Gordon Linoff May 04 '19 at 16:28

SQL Error (Missing Operator) In query operation error

1 Answers1