CODEWITHSUNDEEP
ms-accessvba
saleem saleem
saleem saleem

Reputation: 23

Dates are not updating through sql code in vba

I want to update serial.Issuedate getting from form but its giving syntax error. Please help me how can I correct this error. My code is below:

Private Sub Command30_Click()

Set serialrs = CurrentDb.OpenRecordset("serial")
Dim Idate As Date
Dim Itodo As String
Idate = Me.IssuedDate.Value
Itodo = Me.IssuedToDO.Value

Dim issueqry As String
issueqry = "UPDATE serial " _
& " set serial.IssueToDO =  '" & Itodo & "'" _
& " serial.issuedate = (#" & Format(Idate, "mm\/dd\/yyyy") & "#)" _
& " WHERE (((serial.id) Between 1 And 10)) "

DoCmd.RunSQL issueqry

MsgBox ("Issued Done")

End Sub

Upvotes: 1

Views: 33

Answers (1)

HansUp
HansUp

Reputation: 97101

When you update more than one field, you must include a comma between the field expressions like this ...

SET [field name] = "foo", [another field] = 17
                        ^
                       here

So try your code like this ...

issueqry = "UPDATE serial " _
& " set serial.IssueToDO = '" & Itodo & "'," _
& " serial.issuedate = #" & Format(Idate, "mm/dd/yyyy") & "#" _
& " WHERE serial.id Between 1 And 10"

Also give yourself an opportunity to inspect the string the code built ...

Debug.Print issueqry

You can view the output from Debug.Print in the Immediate window. Ctrl+g will take you there.

Upvotes: 1

Related Questions