Access strSql error

Question

Hello,

I have an strSql (query) I created. One of the parameters of this strSql pulls a value from an open form field. I have the srtSql working up to a certain point, but if I add an Order By criteria I get an error.

Here is what I have working before I add the Order By criteria.

strSql = "INSERT INTO HVE_Invoices_Detail ( HVE_InventoryID, Quantity__Shipped, HVE_InvoicesID ) " & _
"SELECT TblFrmFILL.HVE_InventoryID2, TblFrmFILL.Quantity__Shipped2, HVE_Invoices_Detail.HVE_InvoicesID " & _
"FROM TblFrmFILL, HVE_Invoices INNER JOIN HVE_Invoices_Detail ON HVE_Invoices.HVE_InvoiceID = HVE_Invoices_Detail.HVE_InvoicesID " & _
"GROUP BY TblFrmFILL.HVE_InventoryID2, TblFrmFILL.Quantity__Shipped2, TblFrmFILL.AssignID, HVE_Invoices_Detail.HVE_InvoicesID, HVE_Invoices.HVE_InvoiceID " & _
"HAVING ((TblFrmFILL.AssignID)=2) AND (HVE_Invoices.HVE_InvoiceID)=" & [Forms]![HVE_InvoiceForm]![HVE-InvoiceID] & _

Adding:

"ORDER BY HVE_Invoices_Detail.HVE_InventoryID "

db.Execute strSql, dbFailOnError

Causes

I know it must have something to this line of code "HAVING ((TblFrmFILL.AssignID)=2) AND (HVE_Invoices.HVE_InvoiceID)=" & [Forms]![HVE_InvoiceForm]![HVE-InvoiceID and the " but I can't figure out what I am doing wrong. The form field is a record from a table, but I have to use the field on the form to update the correct selected record. I have tried searching the net but can't find a way to resolve this error.

Answer 1

strSql = "INSERT INTO HVE_Invoices_Detail ( HVE_InventoryID, Quantity__Shipped, HVE_InvoicesID ) " & _
"SELECT TblFrmFILL.HVE_InventoryID2, TblFrmFILL.Quantity__Shipped2, HVE_Invoices_Detail.HVE_InvoicesID " & _
"FROM TblFrmFILL, HVE_Invoices INNER JOIN HVE_Invoices_Detail ON HVE_Invoices.HVE_InvoiceID = HVE_Invoices_Detail.HVE_InvoicesID " & _
"GROUP BY TblFrmFILL.HVE_InventoryID2, TblFrmFILL.Quantity__Shipped2, TblFrmFILL.AssignID, HVE_Invoices_Detail.HVE_InvoicesID, HVE_Invoices.HVE_InvoiceID " & _
"HAVING ((TblFrmFILL.AssignID)=2) AND (HVE_Invoices.HVE_InvoiceID)=" & [Forms]![HVE_InvoiceForm]![HVE-InvoiceID] & _

Adding:

"ORDER BY HVE_Invoices_Detail.HVE_InventoryID "

Hi Harpstrite,

I use to write SQL-strings with the & and separating space in front of the line.

I find it far better to see if you there is something missing:

strSql = "INSERT INTO HVE_Invoices_Detail ( HVE_InventoryID, Quantity__Shipped, HVE_InvoicesID )" _
& " SELECT TblFrmFILL.HVE_InventoryID2, TblFrmFILL.Quantity__Shipped2, HVE_Invoices_Detail.HVE_InvoicesID" _
& " FROM TblFrmFILL, HVE_Invoices INNER JOIN HVE_Invoices_Detail ON HVE_Invoices.HVE_InvoiceID = HVE_Invoices_Detail.HVE_InvoicesID" _
& " GROUP BY TblFrmFILL.HVE_InventoryID2, TblFrmFILL.Quantity__Shipped2, TblFrmFILL.AssignID, HVE_Invoices_Detail.HVE_InvoicesID, HVE_Invoices.HVE_InvoiceID" _
& " HAVING ((TblFrmFILL.AssignID)=2) AND (HVE_Invoices.HVE_InvoiceID)=" & [Forms]![HVE_InvoiceForm]![HVE-InvoiceID] _

& " ORDER BY HVE_Invoices_Detail.HVE_InventoryID "

Imb.

Answer 2

Just as an aside, this is a situation where the error message tells you what's wrong. You see it says 113Order with no space. So Acces is reading the SQL as a InvociceID = 113Order, causing the error.

Answer 3

Hi Daniel,

That fixed it!

I can't believe it was that easy. Thank you so much.

Answer 4

You're simply missing a space before ORDER BY

Try using

" ORDER BY HVE_Invoices_Detail.HVE_InventoryID"