SQL Error (Missing Operator) In query operation error [duplicate]

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
– Mahadev, Commented May 4, 2019 at 13:24
@Andre Looks like it. It's bad I couldn't find that earlier. Thanks for notifying though. — Mahadev
– Mahadev, Commented May 4, 2019 at 13:36

Gordon Linoff · Accepted Answer · 2019-05-04 13:24:10Z

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 4, 2019 at 13:24

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Mahadev Over a year ago

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

Parfait Over a year ago

@Mahadev ... See above linked duplicate. MS Access SQL requires parentheses pairing for queries with more than one JOIN.

Parfait Over a year ago

@GordonLinoff, about that finicky syntax, please see, share, vote, or comment on my recent ticket to the Access team to upgrade its dialect to current standards of which includes this parenthesis requirement that frustrate new users.

Mahadev Over a year ago

@Parfait: Got it. And voted on your ticket.

Gordon Linoff Over a year ago

@Parfait . . . Fortunately, there are many choices of database out there that do support more standard SQL syntax and functionality.

Collectives™ on Stack Overflow

SQL Error (Missing Operator) In query operation error [duplicate]

1 Answer 1

5 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Linked

Related