(Solved) – Why are date values input in a SQL query not equivalent to the same date value in the excel worksheet?

(solved)-–-why-are-date-values-input-in-a-sql-query-not-equivalent-to-the-same-date-value-in-the-excel-worksheet?

Code is below:

If Total_rows_NMRDTRSummary > 1 Then
Dim objConnection As Variant
Dim objRecordset As Variant
Dim connectionString As String
    Const adOpenStatic = 3
    Const adLockOptimistic = 3
    Const adCmdText = &H1

    Set objConnection = CreateObject("ADODB.Connection")
    Set objRecordset = CreateObject("ADODB.Recordset")

    connectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:UsersSun-LapDesktopPayroll Computations (Template).xlsm; Extended Properties=""Excel 8.0; HDR=YES"";"
    objConnection.Open connectionString

    For i = 2 To Total_rows_NMRDTRSummary
        objRecordset.Open "Select * FROM [N-MR Data$] WHERE Person = '" & ThisWorkbook.Worksheets("N-MR DTR Summary").Cells(i, 1) & "' AND Date = " & ThisWorkbook.Worksheets("N-MR DTR Summary").Cells(i, 2), objConnection, adOpenStatic, adLockOptimistic, adCmdText

        Do Until objRecordset.EOF
            Debug.Print objRecordset.Fields.Item("Date"), _
                objRecordset.Fields.Item("Person")
            objRecordset.MoveNext
        Loop

        objRecordset.Close
    Next i
End If

The issue is the objRecordset.EOF is True for the whole For-next loop meaning that there are no matches found in the N-MR Data worksheet that matches a pair in N-MR DTR Summary. This is odd because the same Person and Date columns in N-MR DTR Summary is derived from N-MR Data worksheet. And upon checking, both are dates are have a numeric equivalent in Excel.

The portion with the issue is the Recordset object:

objRecordset.Open "Select * FROM [N-MR Data$] WHERE Person = '" & ThisWorkbook.Worksheets("N-MR DTR Summary").Cells(i, 1) & "' AND Date = " & ThisWorkbook.Worksheets("N-MR DTR Summary").Cells(i, 2), objConnection, adOpenStatic, adLockOptimistic, adCmdText

Hard coding the date into a numeric equivalent will have a match and would work correctly, but if it was in the date format (like the previous example), it would not return any matches. The below code will work as expected:

objRecordset.Open "Select * FROM [N-MR Data$] WHERE Person = '" & ThisWorkbook.Worksheets("N-MR DTR Summary").Cells(i, 1) & "' AND Date = 43594", objConnection, adOpenStatic, adLockOptimistic, adCmdText

Leave a Reply

Your email address will not be published. Required fields are marked *