0

It says invalid insert statement when I run this code. 

CurrentDb.Execute "insert into invoices (Stateid, companyid, PaymentYear,InvoiceDate,Type,MethodofPayment,date,amount) " & _
"values('" & Me.StateID & "','cOOKIE','" & Me.PaymentYear & "', '" & Me.InvoiceDate & "'," _
& "'" & Me.Type & "','" & Me.MethodofPayment & "','" & Me.Date & "','" & Me.Amount & "');"
Martijn Pieters
  • 1,048,767
  • 296
  • 4,058
  • 3,343
cookiemonster
  • 368
  • 1
  • 8
  • 22
  • 1
    [How to debug dynamic SQL in VBA](http://stackoverflow.com/questions/418960/managing-and-debugging-sql-queries-in-ms-access/1099570#1099570) – Andre Jun 20 '18 at 16:04

1 Answers1

1

As a start, provide valid string expressions for the values - not everything is text - and respect reserved words:

CurrentDb.Execute "insert into invoices (Stateid, companyid, PaymentYear, InvoiceDate, [Type], MethodofPayment, [date], amount) " & _
"Values('" & Me.StateID & "','cOOKIE'," & Me.PaymentYear & ", #" & Format(Me.InvoiceDate, "yyyy\/mm\/dd") & "#, " & _
"'" & Me.Type & "','" & Me.MethodofPayment & "',#" & Format(Me.Date, "yyyy\/mm\/dd") & "#," & Str(Me.Amount) & ");"
Gustav
  • 53,498
  • 7
  • 29
  • 55