TSQL INSERT Date Format

[benchpolo]

Public Sub cmdOMWUpdate_Click()
On Error GoTo cmdOMWUpdate_Click_Err

    Dim strSql As String
    Dim curDate As String
    
    DoCmd.SetWarnings False
    
    curDate = Now()
    
    strSql = "INSERT INTO dbo_HEDIS_Quarterly_Letter (type, memberid, language, datemailed, lettersend) " & _
            "SELECT HedisType, MemberID, Language, " & Format(Now(), "mm/dd/yyyy") & ", 'Y' from members"
    
    
    DoCmd.RunSQL strSql
    DoCmd.SetWarnings True

cmdOMWUpdate_Click_Exit:
    Exit Sub

cmdOMWUpdate_Click_Err:
   MsgBox "An unexpected error has occurred." & _
   vbCrLf & "Please note of the following details:" & _
   vbCrLf & "Error Number: " & Err.Number & _
   vbCrLf & "Description: " & Err.Description _
   , vbCritical, "Error"
    Resume cmdOMWUpdate_Click_Exit
    
End Sub

I am trying to insert the current date into a SQL table, and after the code ran the value date value is coming out as 1899-12-30 00:00:00.000 I tried to do a format(Now(),"m m/dd/yyyy") yet it is inserting an incorrect date and format. Please advice.

Thanks.

[benchpolo]

curDate = Format(Now(), "mm/dd/yyyy", vbUseSystemDayOfWeek, vbUseSystem)
    
    strSql = "INSERT INTO dbo_HEDIS_Quarterly_Letter (type, memberid, language, datemailed, lettersend) " & _
            "SELECT HedisType, MemberID, Language, " & Format(Now(), "mm/dd/yyyy") & ", 'Y' from members"

here is the section of the code

[MikeTheBike]

Hi

Having just gone through the loop of querying an SQL Server DB, I think you need to delimit the date with aphostropies (not a Hash as in Access) like this

 strSql = "INSERT INTO dbo_HEDIS_Quarterly_Letter (type, memberid, language, datemailed, lettersend) " & _ 
            "SELECT HedisType, MemberID, Language, '" & Format(Now(), "mm/dd/yyyy") & "', 'Y' from members"

HTH

MTB