Displaying an ADO Recordset in Immediate Window

[desastrux]

Hey everyone, I am stuck on this whole ADO thing. We never used it in class and now I have to use it in my final project. I have the entire project done, except for the last little part, which I can't figure out.

Public Function ADO_ListBooksPublishedYear(yearPublished As Integer)


    Dim rst As ADODB.Recordset
    Dim strSQL As String
    
    strSQL = "Select datepart('yyyy', pubdate) as bookYear from tblBooks"


    Set rst = New ADODB.Recordset
    rst.Open strSQL, CurrentProject.Connection, adOpenStatic, adLockReadOnly, adCmdText
    
    MsgBox "There are " & rst.RecordCount & "records in the unfiltered Recordset"
    
    rst.Filter = "bookYear = yearPublished"
    
    MsgBox "There are " & rst.RecordCount & "records in the filtered Recordset"
    


End Function

This is what I have so far. I can't get the filter to work right either. It won't allow me to use the yearPublished variable in the filter. Also I am supposed to display the results from the Recordset after filtering in the immediate window.

I'm not looking for someone to do this for me, just to steer me in the right direction. I have been searching google for a while trying to figure this out. Would really appreciate any help.

[pbaldy]

As answered elsewhere:

You have to concatenate the variable's value into the filter string. You'd use

Debug.Print

to output to the Immediate window, as shown here:

http://www.baldyweb.com/ImmediateWindow.htm

[June7]

yearPublished is a variable. Must concatenate variables.

rst.Filter = "bookYear=" & yearPublished

Displaying results of recordset in immediate window would involve reading each record and Debug.Print the data from the fields in a loop.

While Not rst.EOF
Debug.Print rst!bookYear
Wend

[pbaldy]

I guess I misread "I'm not looking for someone to do this for me, just to steer me in the right direction."

[June7]

Maybe I got just a little carried away with helpfulness.