An ease way of using ADO Recordset's Filter method to filter a field by multiple values
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 Ifand 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 IfTried 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 IfI 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.