Convert SQL Server date to Access date (or vice-versa) 2

[larrydavid]

Hello,

The versions I'm using are SQL Server 2005 and MS Access 2003 sp3. I have a function which the user will use to update an approve/unapprove bit flag in the SQL table. This is fine, but the dates in the WHERE filter are giving me a problem. The data type for the date in the SQL table is smalldatetime (2013-01-12 13:23:00) and the date format in Access is
(1/12/2013 1:23:00 PM). If someone could please show me how to format either date in my vba code so I don't get a data type mismatch I would greatly appreciate it. The date in question is written as [Test_Date] below.

Private Sub chkTestRecordsApproval_Click()

Dim temp As String
Dim strSQL As String
Dim dbs As DATABASE

Set dbs = CurrentDb()

If Me.chkTestRecordsApproval.Value = True Then
temp = MsgBox("Are you sure you want to Approve this?", vbYesNoCancel)
If temp = vbYes Then
strSQL = "UPDATE dbo_Test_Records_Detail SET [Approved] = 'True' WHERE [Test_Date] = '" & Me.Test_Date & "'"
dbs.Execute (strSQL)
Set dbs = Nothing
Me.chkTestRecordsApproval.Value = True
Else
Me.chkTestRecordsApproval.Value = False
End If
End If

End Sub

Thanks,
Larry

 

[PHV]

WHERE [Test_Date] = '" & Format(Me!Test_Date, "yyyy-mm-dd hh:nn:ss") & "'"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[dhookom]

This might be an issue of both the format and the delimiter. If PH's suggestion doesn't work then consider

WHERE [Test_Date] = #" & Format(Me!Test_Date, "yyyy-mm-dd hh:nn:ss") & "#"

Duane
Hook'D on Access
MS Access MVP

 

[larrydavid]

Wow, thank you Mr. Hookom and PHV. If I could give you both a million stars I would! Mr. Hookom, you've been around as long as I can remember and I really appreciate the help you've given throughout the years. Thanks, Larry