How to create a SQL query string using a cell object as a criteria?

Question

The 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:\Users\Sun-Lap\Desktop\Payroll 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
    Next i
End If

The issue is with the SQL string in 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

The string work if I hard code the string such as: objRecordset.Open "Select * FROM [N-MR Data$] WHERE Person = 'JOHN'"

But doing it in a way where I can incorporate a loop, it will error: objRecordset.Open "Select * FROM [N-MR Data$] WHERE Person = " & ThisWorkbook.Worksheets("N-MR DTR Summary").Cells(i,1)

Error prompt:

I think concatenating strings the usual way in VBA is not followed in the SQL query string? How to properly do it in a way I can feed a loop in the queries?

Answer 1

It looks like you forgot about single quotes for parameter

Person = '" & ThisWorkbook.Worksheets("N-MR DTR Summary").Cells(i, 1) & "' AND...

For date parameter you should do the same thing