Excel Macro to Retrieve Info from Access - SQL Query

[Lee.]

Hi,
I've wrote the following macro but get the error "Run-time error '-2147352571', Could not set the List property. Type mismatch"

I'm struggling to see where the issue lies.

The debug highlights the following line

        .List(i, 0) = rst![CALLBACK_TIME]

Here is the full macro

Private Sub ListBox1_Change()
    Dim cnn As ADODB.Connection
    Dim rst As ADODB.Recordset
    Dim fld As ADODB.Field
    Dim MyConn
    Dim i As Long
    Dim sSQL As String
    Dim search_cat As Date
    search_cat = Me.ListBox1.Value
    
    sSQL = "select Distinct(CALLBACK_TIME) from RETAINED_BANKING where CALLBACK_DATE = #" & search_cat & "# and BOOKING_STATUS = '" & Available & "'"


    Set cnn = New ADODB.Connection
    MyConn = ThisWorkbook.Path & Application.PathSeparator & "Booking_System.mdb"
    
    With cnn
        .Provider = "Microsoft.Jet.OLEDB.4.0"
        .Open MyConn
    End With


    Set rst = New ADODB.Recordset
    rst.CursorLocation = adUseServer
    rst.Open Source:=sSQL, ActiveConnection:=cnn, _
             CursorType:=adOpenForwardOnly, LockType:=adLockOptimistic, _
             Options:=adCmdText
    
    i = 0
    With Me.ListBox2
    .Clear
    Do
        .AddItem
        .List(i, 0) = rst![CALLBACK_TIME]
        i = i + 1
        rst.MoveNext
    Loop Until rst.EOF
    End With


    rst.Close
    cnn.Close
    Set rst = Nothing
    Set cnn = Nothing


End Sub

Many thanks

 

[CharlesChuckieCharles]

Try

rst.movefirst

With Me.ListBox2
.Clear
Do
.AddItem rs.Fields(0).Value
rst.MoveNext
Loop Until rst.EOF

End With


its slow and i'm sure you can connect the recordset directly but since you want to close it this will work

 

[Lee.]

I get the same error.

It appears to be only on some dates.

I've managed to fix it though but formatting the date in American format rather than UK

    search_cat = Format(Me.ListBox1.Value, "MM/DD/YYYY")