CODEWITHSUNDEEP
ms-accessvba
flouwer
flouwer

Reputation: 327

MS Access VBA - Syntax error, truncated update sql statement

I have a subform in datasheet view, whose recordsource is a temporary table. So whenever user inserts new transactions on the form she is inserting new records to temporary table. The subform is set up so the user doesn't have to enter a date for the transaction, meaning that datasheet in the subform does not display the date field. The date is inserted via a textbox in the main form.

Whenever a record is inserted I'm using the subform's AfterUpdate sub and SQL UPDATE statement to insert the date to temporary table. But I'm getting a syntax error. This is the code I'm using:

Dim db As Database
Dim currentID As Integer
Dim transactionDate As Date
Dim strSQL As String

Private Sub Form_AfterUpdate()
    Set db = CurrentDb
    currentID = Me!txtID
    transactionDate = Me.Parent!txtDate

    strSQL = "UPDATE tblTempTable " _
    & "SET transactionDate = " & transactionDate _
    & " WHERE ID = " & currentID & ";"

Debug.Print (strSQL)
db.Execute strSQL

End Sub

As you can see I'm priting the SQL statement right before executing and it prints: UPDATE tblTempTable SET transactionDate = 17.10.2016 WHERE ID = 55; but I'm getting a sytnax error: Syntax error in number in query expression '17.10.201'. When I but the date inside hashtags using & "SET transactionDate = #" & transactionDate & "#" _ I also get a syntax error: Syntax error in date in query expression '#17.10.2016'. At the same time Debug.Print outputs: UPDATE tblTempTable SET transactionDate = #17.10.2016# WHERE ID = 56;

So it seems that the end of the statement is being truncated, but I don't understand why. Can somebody help me?

Upvotes: 0

Views: 157

Answers (2)

Gustav
Gustav

Reputation: 55981

If today's date, use Date:

strSQL = "UPDATE tblTempTable " & _ 
"SET transactionDate = Date() " & _
"WHERE ID = " & currentID & ";"

or use Format:

strSQL = "UPDATE tblTempTable " & _ 
"SET transactionDate = #" & Format(transactionDate, "yyyy\/mm\/dd") & "# " & _
"WHERE ID = " & currentID & ";"

Upvotes: 0

Lynn Crumbling
Lynn Crumbling

Reputation: 13367

Try supplying the date in ISO format:

UPDATE tblTempTable SET transactionDate = #2016-10-17# WHERE ID = 55;

Upvotes: 1

Related Questions