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]

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

 

[desastrux]

well i know that i would use debug.print. but how do i use debug.print to print the recordset?

and how would i concatenate the filter string?

I am putting a year in the main driver as a parameter, and the filter will return all books that have the same year in the published date.

Actually i just realized I am doing it wrong. I need my strSQL to be this

strSQL = "Select * from tblBooks

So how do I go about getting only the year from pubdate inside of the filter?

 

[pbaldy]

Well, you said you just wanted to be steered in the right direction. You'd put the Debug.Print inside a loop of the recordset.

 

[mdlueck]

Also I am supposed to display the results from the Recordset after filtering in the immediate window.

rrrr????? Says who?

Sounds like you are seeking the equivalent of a MySQL query at the Bash prompt, or running a query interactively in SQL Server Management Studio for that matter.

Access / VBA in that regard will drive you insane. When working with ADO objects, you must write code to enumerate through the result set and prepare for display... if in the Immediate Window, then yes lots of calls to Debug.Print to output the strings you prepare with VBA code.

You might like to put a watch on your ADO object and set a break point to stop the code from completing. When the ADO object has been populated, you may open each branch within the ADO object and explore all that is contained within.

 

[pbaldy]

Says the teacher, I imagine.

 

[desastrux]

I have gotten the display in immediate window to work now. The only thing I still haven't been able to do is get the filter to work properly.

i tried to do this but it doesnt work

rst.Filter = "pubdate like '*yearPublished'"

I somehow need to get just the year portion of pubdate. I have tried DatePart and Year functions as well.

 

[ajvol01]

I have gotten the display in immediate window to work now. The only thing I still haven't been able to do is get the filter to work properly.

i tried to do this but it doesnt work

rst.Filter = "pubdate like '*yearPublished'"

I somehow need to get just the year portion of pubdate. I have tried DatePart and Year functions as well.

not sure if this will work, but try it

rst.Filter = "Year(pubdate) = yearpublished"

 

[desastrux]

I have tried that too, I have tried just about any and every combination of things I could think of. Just have no idea what to try now.

 

[ajvol01]

I have tried that too, I have tried just about any and every combination of things I could think of. Just have no idea what to try now.

do me a favor and try this to see if the filter will work without the pubyear

rst.filter = "Year(pubdate) = 2000"

 

[desastrux]

do me a favor and try this to see if the filter will work without the pubyear

rst.filter = "Year(pubdate) = 2000"

Nope, gets the same error as all the others

Error 3001: Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.

Basically I can't use the Year or DatePart functions in the filter. But then I don't know how I could do it, since I have to filter for only those with year 2007.


This is my code for this function now.

Public Function ADO_ListBooksPublishedYear(yearPublished As Integer)

    On Error GoTo Err_ADO_ListBooksPublishedYear

    Dim rst As ADODB.Recordset
    Dim strSQL As String
    
    strSQL = "Select * from tblBooks"
    
    Set rst = New ADODB.Recordset
    rst.Open strSQL, CurrentProject.Connection, adOpenStatic, adLockReadOnly, adCmdText
    
    rst.Filter = "Year(pubdate) = 2007"
    
    Debug.Print "**********************************************************"
    Debug.Print "*****************Books Published In " & yearPublished & "******************" & vbCrLf

    Do While Not rst.EOF
        Debug.Print "Title: " & rst.Fields.Item("title")
        Debug.Print "Published Date: " & rst.Fields.Item("pubdate") & vbCrLf
        rst.MoveNext
    Loop

    Debug.Print "**********************************************************"
    Debug.Print "**********************************************************"
    
    rst.Close
    
Exit_ADO_ListBooksPublishedYear:
    Exit Function
    
Err_ADO_ListBooksPublishedYear:
    MsgBox Err.Number & "  " & Err.Description, vbOKOnly
    Resume Exit_ADO_ListBooksPublishedYear

End Function

 

[ajvol01]

Nope, gets the same error as all the others

Error 3001: Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.

Basically I can't use the Year or DatePart functions in the filter. But then I don't know how I could do it, since I have to filter for only those with year 2007.

last thing I can think of

rst.filter = "pubdate >= #1/1/2007# AND pubdate <= #12/31/2007#"

 

[desastrux]

I actually think i figured it out. But im not at home so i cant test it lol

 

[ajvol01]

I actually think i figured it out. But im not at home so i cant test it lol

i got this to work so you can pass the desired year into the function

Sub FilterTest()
     Call ADO_ListBooksPublishedYear(2007)
End Sub
 
Public Function ADO_ListBooksPublishedYear(yearPublished As Integer)
    On Error GoTo Err_ADO_ListBooksPublishedYear
    Dim rst As ADODB.Recordset
    Dim strSQL As String
 
    Dim dteStart As Date
    Dim dteEnd As Date
 
    dteStart = CDate("1/1/" & yearPublished)
    dteEnd = CDate("12/31/" & yearPublished)
 
    strSQL = "Select * from tblBooks"
 
    Set rst = New ADODB.Recordset
    rst.Open strSQL, CurrentProject.Connection, adOpenStatic, adLockReadOnly, adCmdText
 
    rst.Filter = "pubdate >= " & dteStart & " And pubdate <= " & dteEnd
 
    Debug.Print "**********************************************************"
    Debug.Print "*****************Books Published In " & yearPublished & "******************" & vbCrLf
    Do While Not rst.EOF
        Debug.Print "Title: " & rst.Fields.Item("title")
        Debug.Print "Published Date: " & rst.Fields.Item("pubdate") & vbCrLf
        rst.MoveNext
    Loop
    Debug.Print "**********************************************************"
    Debug.Print "**********************************************************"
 
    rst.Close
 
Exit_ADO_ListBooksPublishedYear:
    Exit Function
 
Err_ADO_ListBooksPublishedYear:
    MsgBox Err.Number & "  " & Err.Description, vbOKOnly
    Resume Exit_ADO_ListBooksPublishedYear
End Function

**********************************************************
*****************Books Published In 2007******************
Title: January 2007 Title
Published Date: 1/1/2007
Title: February 2007 Title
Published Date: 2/1/2007
Title: March 2007 Title
Published Date: 3/1/2007
Title: April 2007 Title
Published Date: 4/1/2007
Title: May 2007 Title
Published Date: 5/1/2007
Title: June 2007 Title
Published Date: 6/1/2007
Title: July 2007 Title
Published Date: 7/1/2007
Title: August 2007 Title
Published Date: 8/1/2007
Title: September 2007 Title
Published Date: 9/1/2007
Title: October 2007 Title
Published Date: 10/1/2007
Title: November 2007 Title
Published Date: 11/1/2007
Title: December 2007 Title
Published Date: 12/1/2007
**********************************************************
**********************************************************