CODEWITHSUNDEEP
vbafilteradorecordset
Graham Griffiths
Graham Griffiths

Reputation: 2216

Get complement of filtered items with ADO recordset

I have an ADO Recordset containing a table of products. I have a list of products used to filter the table - dynamically building a Filter like this (but longer) :

rs.Filter = "productType = 'product A' " _
+ "or productType = 'product B' " _
+ "or productType = 'product C' "

This worked fine for me, I copied the filtered rows into a separate worksheet, all fine.

Then new project requirements meant I needed to get the excluded items as well. Very well, I did some boolean algebra, and ran the same query with an opposite filter :

rs2.Filter = "productType <> 'product A' " _
+ "and productType <> 'product B' " _
+ "and productType <> 'product C' "

This also worked fine. I copy the excluded items into a different worksheet, so now I have both the included and the excluded items.

BUT a new requirement means I have to cope with a special case - product B will only be included on specific dates. Now, this works fine for the positive filter, the one to find included items :

rs.Filter = "productType = 'product A' " _
+ "or (productType = 'product B' and expiry = 16/08/2013) " _
+ "or productType = 'product C' "

but I run into problems with the negative filter (the one to find the excluded items). The following is not allowed due to the restriction on nested OR's in ADO Recordset Filter :

rs2.Filter = "productType <> 'product A' " _
+ "and (productType <> 'product B' or expiry <> 16/08/2013) " _
+ "and productType <> 'product C' "

Is there an alternative solution? E.g. some way to get the complement of a Filter (i.e. just the rows that it excludes)?

I should stress - the list of products to include is built up dynamically, it's not available to me when writing the code.

Upvotes: 0

Views: 1269

Answers (1)

HK1
HK1

Reputation: 12230

I work with ADO quite a bit and I have found only two solutions to work around this limitation in ADO.

The first solution is usually the best one. Just build this filter/where into an SQL statement and query the data source again.

The second option is inefficient but works OK on smaller recordsets. It's a function I wrote that returns a Filtered recordset. What you have to do is call it several times, one time for each working filter (if that makes any sense) so that you end up with the results you want. I don't know how this would work with a connected recordset. I only ever use it with disconnected recordsets.

Public Function GetFilteredRecordset(ByRef rsSource As ADODB.Recordset, _
                                        ByVal sWhere As String, _
                                        Optional ByVal sOrderBy As String, _
                                        Optional ByVal LockType As ADODB.LockTypeEnum = adLockUnspecified) As ADODB.Recordset

    Dim sOriginalOrderBy As String
    sOriginalOrderBy = rsSource.Sort

    rsSource.Filter = sWhere
    If sOrderBy <> "" Then
        If Left(LCase(sOrderBy), 8) = "order by" Then sOrderBy = Trim(Right(sOrderBy, Len(sOrderBy) - 8))
        rsSource.Sort = sOrderBy
    End If

    Dim objStream As ADODB.Stream
    Set objStream = New ADODB.Stream
    rsSource.Save objStream, adPersistXML

    Dim rsF As ADODB.Recordset
    Set rsF = New ADODB.Recordset
    rsF.Open objStream, , , LockType

    rsSource.Filter = ""
    rsSource.Sort = sOriginalOrderBy

    objStream.Close
    Set objStream = Nothing
    Set GetFilteredRecordset = rsF

End Function

Upvotes: 0

Related Questions