An ease way of using ADO Recordset's Filter method to filter a field by multiple values

Tuesday, May 25, 2004

Yesterday, I needed to filter an ADO Recordset field by multiple values, so I first tried:

'myFilterList = "1,3,4,5"
If myFilterList & vbNullString <> vbNullString Then
    MyRecordset.Filter = "MyField IN (" & myFilterList & ")"
End If

and just found out it did not work because "IN" is not a valid operator for the Filter method. So to workaround this I rewrote code as follow:

'myFilterList = "1,3,4,5"
If myFilterList & vbNullString <> vbNullString Then
    MyRecordset.Filter = "MyField = " & Join(Split(myFilterList, ","), " OR MyField = ")
End If

Tried it with only one value in the list as below and it also worked correctly

'myFilterList = "2"
If myFilterList & vbNullString <> vbNullString Then
    MyRecordset.Filter = "MyField = " & Join(Split(myFilterList, ","), " OR MyField = ")
End If

I have not tried it yet with ADO.NET, but believe the problem with the "IN" operator persists. What do you think about this code. Just drop me a line and let me know your opinion about it.

1 Comment