Dates are not updating through sql code in vba

Question

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

I don't know enough about VBA but Format function probably does not need \/. You might be able to write Format(Idate, "mm/dd/yyyy") — zedfoxus
– zedfoxus, Commented Mar 29, 2014 at 5:42

HansUp · Accepted Answer · 2014-03-29 05:58:23Z

1

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.

edited Mar 29, 2014 at 5:58

answered Mar 29, 2014 at 5:53

HansUp

97.3k11 gold badges80 silver badges139 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Dates are not updating through sql code in vba

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related