syntax error in sql?

[llaman]

This returns a syntax error and it seems to match other examples i see in the forum. Any ideas what could be wrong with it?


Sub Update_Invoice(new_key As Integer, company_id As Integer, invoice_date As Date)

Dim SQL As String

SQL = "INSERT INTO " & "Invoice Purchase " & _
"(" & new_key & ", " & customer_id & ", " & new_key & ", " & invoice_date & ", 0) "

DoCmd.RunSQL SQL

End Sub

 

[llaman]

I still have not figured out what could be wrong with the INSERT INTO statement though... any ideas?

SQL = "INSERT INTO Invoice_Purchase (invoice_id, customer_id, item_line, date, ShipID)" & _
" VALUES (" & new_key & ", " & company_id & ", " & new_key & ", #" & invoice_date & "#, 0)"

(assume end of lines are correct)

It responds with a syntax error. I have tried for a long time but I can't figure it out.

 

[Corticus]

The 'date' here:
INSERT INTO Invoice_Purchase (invoice_id, customer_id, item_line, date, ShipID)"
is the problem. Date is a reserved word in Access, try changing the name of that field and update the above expression to reflect this change.