I have an Sql update statement that I am using that updates the data from a record chosen by the user from a list box lstDelFrom.
This is working for all the text fields & updates fine. Once I add in one of the fields that is a Date field I cannot get the update to work.
ie) this line “[delegation].[on] = #” & Nz(rst.Fields(9).Value, “”) & “#,” & _
Could someone assist me with the correct syntax to use.
I also need to consider that the field may be null if nothing was entered in against the date field.
Here is my code so far. The field I am trying to update is called [delegation].[on]
It is defined as a short date in the delegation table.
sqlupdate = “update delegation ” & _
“set [delegation].[seg] = ‘” & Nz(rst.Fields(1).Value, ” “) & “‘,” & _
“[delegation].[other] = ‘” & Nz(rst.Fields(2).Value, ” “) & “‘,” & _
“[delegation].[name] = ‘” & Nz(rst.Fields(3).Value, ” “) & “‘,” & _
“[delegation].[title] = ‘” & Nz(rst.Fields(4).Value, ” “) & “‘,” & _
“[delegation].[section] = ‘” & Nz(rst.Fields(5).Value, ” “) & “‘,” & _
“[delegation].[keywords] = ‘” & Nz(rst.Fields(6).Value, ” “) & “‘,” & _
“[delegation].[adopted] = ‘” & Nz(rst.Fields(8).Value, ” “) & “‘,” & _
“[delegation].[on] = #” & Nz(rst.Fields(9).Value, “”) & “#,” & _
“where ([delegation].[id] = ‘” & [Forms]![frmdelnewproperties]![id] & “‘)”
MsgBox “Copying details from Delegation – ” & lstDelFrom.Column(0) & ” ” & lstDelFrom.Column(3) & “-” & lstDelFrom.Column(4)
wrk.BeginTrans
dbs.Execute sqlupdate
wrk.CommitTrans
Thanks for any assistance that anyone may be able to give me.