Hi,
I have issue including a Date in SQL beneath.
Following works but gives wrong date (expected) in database:
UPDATE TStaffAssignedToJob SET JobReport_Sent = 2018-11-14 WHERE ID_StaffAssignedToJob = 7359
Following doesn't work. I thought it should work. Error message "Incorrect syntax near '#'":
UPDATE TStaffAssignedToJob SET JobReport_Sent = #2018-11-14# WHERE ID_StaffAssignedToJob = 7359
Code:
Public Const gsConn As String = "Provider=SQLOLEDB;" _
& "Data Source=ksds0816b;" _
& "Initial Catalog=IP-Planning;" _
& "Integrated Security=SSPI;"
Sub ActionQuery_Update()
Dim cIP As ADODB.Connection
Dim sSQL As String
Set cIP = New ADODB.Connection
cIP.Open gsConn
'Works at "cIP.Execute (sSQL)" but gives wrong date
'UPDATE TStaffAssignedToJob SET JobReport_Sent = 2018-11-14 WHERE ID_StaffAssignedToJob = 7359
sSQL = "UPDATE TStaffAssignedToJob SET JobReport_Sent = " & Date _
& " WHERE ID_StaffAssignedToJob = " & 7359
'...
cIP.Execute (sSQL)
'Create Error at "cIP.Execute (sSQL)"
'UPDATE TStaffAssignedToJob SET JobReport_Sent = #2018-11-14# WHERE ID_StaffAssignedToJob = 7359
sSQL = "UPDATE TStaffAssignedToJob SET JobReport_Sent = #" & Date & "#" _
& " WHERE ID_StaffAssignedToJob = " & 7359
'...
cIP.Execute (sSQL)
cIP.Close
Set cIP = Nothing
End Sub