vba - Get complement of filtered items with ADO recordset -

i have ado recordset containing table of products. have list of products used filter table - dynamically building filter (but longer) :

rs.filter = "producttype = 'product a' " _ + "or producttype = 'product b' " _ + "or producttype = 'product c' " 

this worked fine me, copied filtered rows separate worksheet, fine.

then new project requirements meant needed excluded items well. well, did boolean algebra, , ran same query opposite filter :

rs2.filter = "producttype <> 'product a' " _ + "and producttype <> 'product b' " _ + "and producttype <> 'product c' " 

this worked fine. copy excluded items different worksheet, have both included , excluded items.

but new requirement means have cope special case - product b included on specific dates. now, works fine positive filter, 1 find included items :

rs.filter = "producttype = 'product a' " _ + "or (producttype = 'product b' , expiry = 16/08/2013) " _ + "or producttype = 'product c' " 

but run problems negative filter (the 1 find excluded items). following not allowed due 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 alternative solution? e.g. way complement of filter (i.e. rows excludes)?

i should stress - list of products include built dynamically, it's not available me when writing code.

i work ado quite bit , have found 2 solutions work around limitation in ado.

the first solution best one. build filter/where sql statement , query data source again.

the second option inefficient works ok on smaller recordsets. it's function wrote returns filtered recordset. have call several times, 1 time each working filter (if makes sense) end results want. don't know how work connected recordset. ever use disconnected recordsets.

public function getfilteredrecordset(byref rssource adodb.recordset, _                                         byval swhere string, _                                         optional byval sorderby string, _                                         optional byval locktype adodb.locktypeenum = adlockunspecified) adodb.recordset      dim soriginalorderby string     soriginalorderby = rssource.sort      rssource.filter = swhere     if sorderby <> ""         if left(lcase(sorderby), 8) = "order by" sorderby = trim(right(sorderby, len(sorderby) - 8))         rssource.sort = sorderby     end if      dim objstream adodb.stream     set objstream = new adodb.stream     rssource.save objstream, adpersistxml      dim rsf 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