VBA SQL Select statement Concatenate

Question

I have an Excel sheet with a drop down list of today's date + 4 days ahead, when you select a date I want to show the collections for that date, the collections are of datetime on another worksheet, In my select statement I want to concatenate the dateBox variable text to the cptString variable text and only show the collections for that datetime. Please find below the code I am having issue with.

Private Sub dateBox_Change()
    Dim connection As New ADODB.connection
    
    connection.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.FullName & _
    ";Extended Properties=""Excel 12.0;HDR=YES;"";"
    
    Dim dateQuery As String
    Dim queryString As String
    Dim cptString1 As String
    Dim cptString2 As String
    Dim cptString3 As String
    
    dateQuery = Me.dateBox.Text
    
    cptString1 = "00:30"
    cptString2 = "01:30"
    cptString3 = "02:00"

    queryString = "Select [Lane],[Containerized Packages],[Staged Packages]," & _
    "[Loaded Packages],[Departed Packages],[Expected Packages],[All Packages] " & _
    "from [Data$] where " & 
    "[Lane] = 'SBS2->CC-RM-SWANSEA-GB-H2' or " & _
    "[Lane] = 'SBS2->CC-RM-Cardiff-GB-H2' or " & _
    "[Lane] = 'SBS2->CC-RM-Bristol2-GB-H2' " & _
    "and [CPTs] = dateQuery """ & cptString1 & """ "
    
    
    Dim rs As New ADODB.Recordset
    rs.Open queryString, connection
    
    Dim rSht As Worksheet
    Set rSht = ThisWorkbook.Worksheets("Sheet1")
    
    With rSht
        .Cells.ClearContents
        For i = 0 To rs.Fields.Count - 1
            .Cells(4, i + 1).Value = rs.Fields(i).Name
        Next i
        .Range("A5").CopyFromRecordset rs
    End With
    
    connection.Close
End Sub

Answer 1

This (casting date-time to a numeric value) worked for me:

Private Sub dateBox_Change()
    Dim connection As New ADODB.connection, dttime
    
    connection.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.FullName & _
                    ";Extended Properties=""Excel 12.0;HDR=YES;"";"
    
    Dim dateQuery As String
    Dim queryString As String
    Dim cptString1 As String
    Dim cptString2 As String
    Dim cptString3 As String
    
    'dateQuery = Me.dateBox.text
    
    cptString1 = "00:30"
    cptString2 = "01:30"
    cptString3 = "02:00"
    
    'create a numeric datetime value
    dttime = 1 * (DateValue("05/08/2024") + TimeValue(cptString1))

    queryString = "Select [Lane],[Containerized Packages],[Staged Packages]," & _
        " [Loaded Packages],[Departed Packages],[Expected Packages],[All Packages] " & _
        " from [Data$] where " & _
        " [Lane] in('SBS2->CC-RM-SWANSEA-GB-H2','SBS2->CC-RM-Cardiff-GB-H2','SBS2->CC-RM-Bristol2-GB-H2')" & _
        " and [CPTs]*1 = " & dttime  '*1 converts to numeric value
    
    Dim rs As New ADODB.Recordset
    rs.Open queryString, connection
    
    If Not rs.EOF Then
        'just print the forst matched Lane value
        Debug.Print rs("Lane").Value '>> SBS2->CC-RM-Cardiff-GB-H2
    Else
        Debug.Print "No hits"
    End If
    
    rs.Close
    connection.Close
    
End Sub

Test data: