syntax error (missing operator) in query expression sri.invoiceNo = sri.invoiceNo INNER JOIN Staff s oN s.Staffid = sr.userld

1
votes
"SELECT SalesReturnId, ReturnDate, sr.InvoiceNo, (lastname & ', ' & firstname & ', ' &  MI) as StaffName, TotalAmount, SUM(sri.Quantity) as TotalQuantity FROM SalesReturn sr INNER JOIN SalesReturnItem sri ON sr.InvoiceNo = sri.InvoiceNo INNER JOIN Staff s ON s.StaffId = sr.userID WHERE ReturnDate  BETWEEN '" + startDate.ToString("yyyy-MM-dd") + "' AND '" + endDate.ToString("yyyy-MM-dd") + "' AND sr.InvoiceNo LIKE '%" + txtName.Text + "%' GROUP BY sr.InvoiceNo ORDER BY ReturnDate, sr.InvoiceNo DESC";

When I run that query I keep getting this error:

syntax error (missing operator) in query expression sri.invoiceNo = sri.invoiceNo INNER JOIN Staff s oN s.Staffid = sr.userld

1
mysqlsqlms-access
If this is Access, copy and paste it into the query designer - you miss some parenthesis around the joins. – Gustav
Add line breaks. – jarlh

1 Answers

1
votes

In Access you need parentheses when you have more than one join:

SELECT SalesReturnId, ReturnDate, sr.InvoiceNo, 
(lastname & ', ' & firstname & ', ' &  MI) as StaffName,
TotalAmount, SUM(sri.Quantity) as TotalQuantity 
FROM (SalesReturn sr 
INNER JOIN SalesReturnItem sri ON sr.InvoiceNo = sri.InvoiceNo )
INNER JOIN Staff s ON s.StaffId = sr.userID 
WHERE ReturnDate  BETWEEN '" + startDate.ToString("yyyy-MM-dd") + "' AND '" + endDate.ToString("yyyy-MM-dd") + "' 
AND sr.InvoiceNo LIKE '%" + txtName.Text + "%' 
GROUP BY sr.InvoiceNo 
ORDER BY ReturnDate, sr.InvoiceNo DESC;