SQL statement for ADODB recordset.filter method

[suvarna]

I want to use the NOT IN operator for the filter method of ADODB.recordset in VBA. Do anybody in the forum know the syntax.

 

[RoyVidar]

I don't think you can use that operator with the ADO filter method, as it is performed on an existing recordset (not the table, query or SQL it's based on). The help file specifies

"Operator must be one of the following: <, >,<=, >=, <>, =, or Like"

One workaround, would probably be (re)opening the/a recordset with a new where clause (including a subquery), if you can't build a filter with

rs.filter = "field <> " & somevalue & " and field <> " & someothervalue & " and...

 

[suvarna]

Thanks RoyVidar. Yes the NOT IN operator doesn't work with ADODB.filter method. We have to do some round about.