ADO Recordset Filter using IN Clause

Posted on 2005-03-23
Medium Priority
Last Modified: 2008-01-09
I am using Microsoft ADO Object library 2.5 in my Visual Basic project. I am facing the problem using filter method when i am specifying IN clause. If ADO filter supports IN clause then can u please send some examples.

   my code is something like this:
    rs.Filter = "ID in (22,23,25)"

where ID is number field.

Question by:s_monani
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 12

Expert Comment

by:Bob Lamberson
ID: 13619181
rs.Filter = "(((ID)=22 Or (ID)=23 Or (ID)=24))"


Author Comment

ID: 13619188
The list which i want to compare is very long. Not possible with an OR operator which is y i want to use an IN clause
LVL 53

Expert Comment

ID: 13619554
Perhaps you should consider then closing your recordset and rebuild the sql-query

strSql = "Select * from YourTable where ID in (22,23,25)"
rs.Open strSql, YourConnection, adOpenDynamic, adLockPessimistic
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


Author Comment

ID: 13619725
I dont want a where clause. i want to incorporate IN clause using recordset.filter

Accepted Solution

_lv_ earned 250 total points
ID: 13619868
First you should carefuly read ADO documentation (Filter property).
There you will find, that 'Filter' clause is in the form FieldName-Operator-Value, where:

  - FieldName must be a valid field name from the Recordset. If the field name contains spaces, you must enclose the name in square brackets.

  - Operator must be one of the following: <, >, <=, >=, <>, =, or LIKE.

(according to ADO2.8)

Guess this is the answer. The answer is NO (YES, "Filter property does not support IN clause").
Consider using suggested ordinary SQL query.
LVL 12

Expert Comment

by:Bob Lamberson
ID: 13622121
Mine and Dhaest are the only two options. I would use Dhaest's idea. The filter option is slow by comparison to the sql option. especially if you have a lot of conditions.
Is there some reason you are not stating that you don't want to use the sql method?


Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Suggested Courses
Course of the Month14 days, 22 hours left to enroll

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question