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

 

[Corticus]

The date datatype must be marked with #

So try this:
SQL = "INSERT INTO " & "Invoice Purchase" & _
"( " & new_key & ", " & customer_id & ", " & new_key & ", #" & invoice_date & "#, 0) "

I'm assuming new_key is a numeric value, let me know if this is otherwise.

You can use the immediate window (with a code break on the SQL= line) to test the value of SQL when defined, then plug the SQL into a new query and Access will tell you any problems, just for future reference.

Data types of dates must be enclosed in # like #12/01/04#
Text datatypes must be enclosed in quotes like "Bob"

To concatenate a quote into a string, use 3 quotes like
SQL = "Insert Into names( """ & txtName & """ );"

Hope this helps,

 

[llaman]

I tried your suggestion, however it still comes back with a syntax error. Here is exactly what I put in.

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

 

[Norie]

Which line is causing the error?

 

[bmacr]

This may not make a difference, but I noticed that SQL is a reserved word in Access. Perhaps use something like strSQL instead for your variable name.

 

[llaman]

No, it does not make a difference. I have other queries that run off the same use of the word SQL

Thanks for the suggestion though

 

[Corticus]

What is the error?

 

[llaman]

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

Dim SQL As String

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

DoCmd.RunSQL SQL

Here is the updated try. Still not working. It still says there is a "syntax error" on the execution of the DoCmd.RunSQL




This recieves the same error

Sub Update_ShipID(new_key As Integer, zip As Long)

Dim SQL As String

SQL = "UPDATE Invoice_Purchase" & _
"SET Invoice_Purchase.shipID = " & new_key & _
" WHERE " & zip & " = Customer.zip AND Customer.customer_id = Invoice_Purchase.customer_id" & _
" AND Invoice_Purchase.shipID = 0"

DoCmd.RunSQL SQL

End Sub

 

[Norie]

How are you trying to run this?

BTW there should be a space before SET - I don't know if that's just a typo.

EDIT: When I run the code with the space in front of SET I don't get a syntax error
but one about not being able to find the table (which isn't unusual because I don't have a table with that name)

 

[llaman]

Thanks for everyone trying to help, You were correct about the space, however it had the problem afterwards of not being able to read from table Customer and asking for parameters. At this point I restructured it completely and this code now works:

SQL = "UPDATE Invoice_Purchase" & _
" INNER JOIN Customer ON Invoice_Purchase.customer_id = Customer.customer_id" & _
" SET Invoice_Purchase.shipID = " & new_key & _
" WHERE Customer.zip = " & zip & " AND Invoice_Purchase.shipID = 0"