Action Query Update - SQL with Date

[Joakim N]

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

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

[ranman256]

y-m-d , thats not really a date format,
use: m/d/y

and sql wants it surrounded by # . (like in your second sql)

[Minty]

If you are sending this as a SQL Server command then the date would need to be sent as

'2018-11-14'

In Access you can safely use this format

#2018/11/14#

in any environment / locale

[isladogs]

In fact you can also use the yyyy-mm-dd format safely in Access SQL.
One advantage of that format is that dates can be easily sorted visually.

[Joakim N]

Thanks all,

This worked. I had to change " # " to " ' ":
sSQL = "UPDATE TStaffAssignedToJob SET JobReport_Sent = '" & Date & "'" & " WHERE ID_StaffAssignedToJob = " & 7359

(Maybe I wasn't clear enough in my original info. My Front End is in Access. My databases are stored on SQL Server. However, the code above shall be used from VBA Excel.)

Thanks again!