Really Long ADO Filter Strings?

[ClimoC]

'Ello

Have some recordsets. Nice.

A master doc connects to some csv's with the oledb text connection, dumps them all to a sheet, and simultaneously builds a 'master' recordset to do some hard crunching.

Part of this is a userform that allows you to pick Fields, Operators and Values from Drop-Down/Textboxes, to build your own filter string (with an extra 2 textboxes for dates)

The date + String builder works brilliantly (if I do say so myself, though the code is entry level for this level of project, really). But now I'm trying to add another level - a listbox that allows you to multi-select which databases you apply the filter to.


The working part is this:

Master.Filter = adFilterNone
Master.MoveFirst
FilterString = ""
If ListBox1.ListCount < 2 Then OneOrNone = True Else OneOrNone = False
For i = 0 To ListBox1.ListCount - 1
        TField = ListBox1.List(i, 0)
        TOperator = Translate(ListBox1.List(i, 1))
        TCriteria = ListBox1.List(i, 2)
        
        Select Case True
                Case TField = "Timeslot"
                                    TCriteria = CDbl(VBA.TimeSerial(Hour:=Left(TCriteria, 2), Minute:=Right(TCriteria, 2), Second:="00"))
                Case TField = "WeekDay"
                          Select Case TCriteria
                                Case "Monday"
                                    TCriteria = "$1"
                                Case "Tuesday"
                                    TCriteria = "$2"
                                Case "Wednesday"
                                    TCriteria = "$3"
                                Case "Thursday"
                                    TCriteria = "$4"
                                Case "Friday"
                                    TCriteria = "$5"
                                Case "Saturday"
                                    TCriteria = "$6"
                                Case "Sunday"
                                    TCriteria = "$7"
                                Case "Monday-Friday"
                                        If TOperator = "=" Then
                                                    TOperator = "<="
                                                    tcrtieria = "$5"
                                        Else
                                                    TOperator = ">="
                                                    TCriteria = "$6"
                                        End If
                                Case "Saturday-Sunday"
                                        If TOperator = "=" Then
                                                    TOperator = ">="
                                                    TCriteria = "$6"
                                        Else
                                                    TOperator = "<="
                                                    TCriteria = "$5"
                                        End If
                      End Select
                      
                Case TField = "EpisodeNumber"
                        Master.Filter = TField & " " & TOperator & " $" & CLng(TCriteria)
                Case TField = "Premiere"
                        TField = "PremierOrRepeat"
                        
                Case Else

        End Select
        
                        If TOperator = "Like" Then
                                TCriteria = "*" & TCriteria & "*"
                        Else
                                TCriteria = "'" & TCriteria & "'"
                       End If
                        If OneOrNone = True Then
                                FilterString = "[" & TField & "] " & TOperator & " " & TCriteria
                        Else
                                If i < 1 Then
                                        FilterString = "[" & TField & "] " & TOperator & " " & TCriteria
                                Else
                                        FilterString = FilterString & " AND [" & TField & "] " & TOperator & " " & TCriteria
                                End If
                        End If
        
Next

If Not FilterString = "" And Not FilterString = "[]  ''" Then
FilterString = "[Start] >= #" & CDate(Me.TextBox1.Value) & "# AND [Start] <= #" & CDate(Me.TextBox2.Value) & "# AND " & FilterString
Else
FilterString = "[Start] >= #" & CDate(Me.TextBox1.Value) & "# AND [Start] <= #" & CDate(Me.TextBox2.Value) & "#"
End If

That works great. Allows for combo's of text, date, and string criteria filters.

Now I'm trying to add this part. the 'Channel' is things like 'Finance', 'Support', etc. About 14 channels in all.

Dim pi As Integer
pi = 1
For i = 0 To (Me.ListBox3.ListCount - 1) Step 1
    If Me.ListBox3.Selected(i) Then
        If Not pi = 1 Then
            FilterString = FilterString & " OR [Channel] = '" & Me.ListBox3.List(i) & "'"
        Else
            FilterString = FilterString & " AND ([Channel] = '" & Me.ListBox3.List(i) & "'"
            pi = 2
        End If
    End If
Next i
FilterString = FilterString & ")"

The two separate filter strings work fine on their own (The 'Channel' one just a long series of 'OR's), but together they bug as "Run0Time 3001 :Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another"

Is this because I have to re-write the string more like this?:

Filterstring = "([Channel] = '" & me.listbox3.list(i) & "' AND [Start] <= #" & date1 & "# AND [Start] >= #" & date2 & "# AND [" & TField & "] " & TOperator & " " & TCriteria & ") OR ([Channel = '" & me.listbox3.list(i) & "'" AND...

...
...

so having the Date criteria, custom string criteria, and each channel, all bound up into individual parentheses? (For all c. 12 channels? So 12 x the 'Date' and 'TCriteria' bits?)

'cause if it is, I'm screwed! The current string is about 700 chars long, and works, but I think I checked and the limit for an ADO recordset filterstring is c. 1000 chars?

I'm just trying to keep it elegant and dynamic. An almost identical version of this, when it came to the listbox as a database-filter, I cheated and got a dictionary to hold all the database names that were selected, then ran through and 'deleted' all records that didn't match that criteria in my channel column.

This time, since I'd like people to be able to enter criteria, search, and then change criteria without having to cache all the DB's again, I wanted to include it as part of the string.

Suggestions? Help? Advice?

Thanks
C

 

[Jon von der Heyden]

I confess I didn't read all the code and test the strings exhaustively (they are rather long). However, you can apply filters to recordsets recursively. What I mean is why don't you filter the recordset with a shorter and less complicated filter expression. Then filter it again for the next bit, and so on and so forth.

You may find this article on how to make a recordset filter stick helpful too...

 

[ClimoC]

Darnit. Never mind. Seen the problem. I didn't read your post in the link with the proper eyes on.

Quick Q though, what's the difference between saving a 'stream' and just cloning the recordset and filtering on that? I imagine it's got something to do with being able to 'unfilter' back to the 'start'? But if you've got 2 recordsets, one a 'master', and just filter and filter and filter a clone of that (clone created at runtime when you 'reapply your filterS')

 

[Jon von der Heyden]

You need the Stream because you cannot clone a filtered recordset and have the filter persist.

So you would need e.g:

Sub Demo()
Dim strFilter as String
Dim recMain as ADODB.Recordset
Dim recTemp as ADODB.Recordset

'some stuff here
'strFilter string variable takes on some expression

[COLOR="DarkGreen"]'// Filter the main recordset in the 1st pass, but load filtered results to a helper recordset object variable[/COLOR]
Set recTemp = FilterRecordset([highlight]recMain[/highlight], strFilter)

'some more bits here
'strFilter takes on a new expression

[COLOR="DarkGreen"]'// From this point on you will always filter the helper recordset[/COLOR]
Set recTemp = FilterRecordset([highlight]recTemp[/highlight], strFilter)

'more stuff
'don't forget to destroy your recordsets
End Sub

Note, the Master recordset remains unfiltered throughout, rather we use a helper...

 

[ClimoC]

Had to make a quick adjustment to your function, as the process of saving the stream saves the 'Original' in the filtered state - which for the purposes of being able to remove the custom make filter strings (ie 'go up one level' of filtering), I need to be able to start from the original, untampered recordset each time the filters are reapplied.

Probably one line too many, but have amended to this:

Public Function FilterRecordset(ByVal objRecordSet As ADODB.Recordset, ByVal strExpression As String) As ADODB.Recordset
    Dim objStream As ADODB.Stream, origRecordSet As ADODB.Recordset
    Set origRecordSet = objRecordSet.Clone
    With objRecordSet
        If .RecordCount = 0 Then Exit Function
            
        On Error Resume Next
            .Filter = strExpression
        On Error GoTo 0
        
        Set objStream = New ADODB.Stream

        .Save objStream, adPersistXML
        .Close
        .Open objStream
    End With
        
    Set FilterRecordset = objRecordSet
    Set Master = origRecordSet.Clone
    Set objRecordSet = Nothing
    Set origRecordSet = Nothing
End Function

...where 'Master' is the Global Recordset original.. (ie the Master!)

Thanks very much Jon. Will be employing your function many times in the future I can tell!

Thanks
C