ADO Find method

[Keith]

I would like to know if there is a way to use the ADO Find method to find one
of several values for a single column. See the following example:

rstOpenJobProcess.Find "[proc#] = '940' or '941'"

I would like to search for proc# '940' or '941'. I want to avoid using a
like statement in the criteria (such as [proc#] Like '94*') because I may use
'942' for something else in the future that I would not want to search for.
Is there a way to use the Find method in this way? Alternatively, is there a
clever workaround?

Thanks,

Keith

 

[Dirk Goldgar]

I would like to know if there is a way to use the ADO Find method to find
one
of several values for a single column. See the following example:

rstOpenJobProcess.Find "[proc#] = '940' or '941'"

I would like to search for proc# '940' or '941'. I want to avoid using a
like statement in the criteria (such as [proc#] Like '94*') because I may
use
'942' for something else in the future that I would not want to search
for.
Is there a way to use the Find method in this way? Alternatively, is
there a
clever workaround?

Try this:

rstOpenJobProcess.Find "[proc#] In ('940', '941')"

That's assuming that [proc#] is a text field, as your original code
suggests.

 

[Keith]

Dirk,

Thanks so much for the response. This looks like exactly what I'm asking
for. Unfortunately, when I use this code, I get the following error message:

"Arguments are of the wrong type, are out of acceptable range, or are in
conflict with one another."

[proc#] is a text field as you assumed, so that's not the issue.

I have figured out a workaround, basically using the find method twice and
determining which result is what I want. It's clunky, but it will work. If
you have any other thoughts, please let me know. I could see this coming up
again in the future.

Thanks again,

Keith

I would like to know if there is a way to use the ADO Find method to find
one
of several values for a single column. See the following example:

rstOpenJobProcess.Find "[proc#] = '940' or '941'"

I would like to search for proc# '940' or '941'. I want to avoid using a
like statement in the criteria (such as [proc#] Like '94*') because I may
use
'942' for something else in the future that I would not want to search
for.
Is there a way to use the Find method in this way? Alternatively, is
there a
clever workaround?

Try this:

rstOpenJobProcess.Find "[proc#] In ('940', '941')"

That's assuming that [proc#] is a text field, as your original code
suggests.

--
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

(please reply to the newsgroup)

 

[vanderghast]

ADO recordset Find method support only one test. Use successive test on
successive clones as workaround (http://support.microsoft.com/kb/195222) or
use an SQL query which will pump the primary key value for your complex
criteria and feed the ADO method with a test on that later.


Vanderghast, Access MVP

 

[Dirk Goldgar]

Dirk,

Thanks so much for the response. This looks like exactly what I'm asking
for. Unfortunately, when I use this code, I get the following error
message:

"Arguments are of the wrong type, are out of acceptable range, or are in
conflict with one another."

Drat! I'd hoped that phrasing the criterion as an In() expression would get
past ADO's restriction on the Find method. But no, it only allows certain
comparison operators and a single field and value to be tested.

I have figured out a workaround, basically using the find method twice and
determining which result is what I want. It's clunky, but it will work.
If
you have any other thoughts, please let me know. I could see this coming
up
again in the future.

Vanderghast has pointed you to a KB article that may give you some
alternatives.