Find Method with Multiple Criteria

[Karl Robinson]

Hi,

I try to use the FIND method of an ADO recordset object with this criteria,
Field_1='abc' AND Field_2='xyz'
It returns an error 3001.
In fact, on the form, users browse thousands of records from a MSFlexGrid
and they can input values for different fields everytime to find the record
they want. Thus, the criteria can be a combination of any fields of the
recordset. If record found, highlight the record in the MSFlexGrid.

Can anyone give me some hits for doing this ?

Thanks a lot.
Karl

[Arthur Wood]

Karl,

The FIND method is limited to a SINGLE criterion. that is how it is defined.
The only other choice is to build the recordset (SQL Query) to retrieve
the desired records:

set rs=DB.CreateRecordset("Select <field> from <table> WHERE Field_1 = 'abc'
AND Field_2 = 'xyz'")

Soory but those are the limis to the Find Method...look up Find Method in
the VB Help System.

Arthur Wood



"Karl Robinson" <kklo@hehe.com> wrote:
>
>Hi,
>
>I try to use the FIND method of an ADO recordset object with this criteria,
>Field_1='abc' AND Field_2='xyz'
>It returns an error 3001.
>In fact, on the form, users browse thousands of records from a MSFlexGrid
>and they can input values for different fields everytime to find the record
>they want. Thus, the criteria can be a combination of any fields of the
>recordset. If record found, highlight the record in the MSFlexGrid.
>
>Can anyone give me some hits for doing this ?
>
>Thanks a lot.
>Karl

[Harvey Triana]

Karl-
..MoveFirst
..Find "Field_1='abc'"
if not .EOF Then
.Find "Field_2='xyz'"
if not .EOF Then
'Find is done
end if
end if

-It is optimum
________________________________
Harvey Triana... psoft@latino.net.co
Microsoft Developer MVP- Visual Basic
www.eidos.es/VeXPERT

"Karl Robinson" <kklo@hehe.com> escribió en el mensaje
news:3b58e59d$1@news.devx.com...
>
> Hi,
>
> I try to use the FIND method of an ADO recordset object with this
criteria,
> Field_1='abc' AND Field_2='xyz'
> It returns an error 3001.
> In fact, on the form, users browse thousands of records from a MSFlexGrid
> and they can input values for different fields everytime to find the
record
> they want. Thus, the criteria can be a combination of any fields of the
> recordset. If record found, highlight the record in the MSFlexGrid.
>
> Can anyone give me some hits for doing this ?
>
> Thanks a lot.
> Karl

[Arthur Wood]

Harvey,
That will only succeed in finding the first record which has Field_2 =
'xyz' and that may OR MORE LIKELY MAY NOT be a record which ALSO has Field_1
= 'abc'.

The fact that the first FIND suceeded DOES NOT restrict the recordset to
ONLY those records for whcih Field_1 has the value 'abc'. It simply moves
the record pointer to the first record, in the ENTIRE recordset, that has
Field_1 = 'abc'. The Second Find them simply moves the record pointer to
the first record (in the same ENTIRE record set) that has Field_2 = 'xyz'.

Arthur Wood


"Harvey Triana" <psoft@latino.net.co> wrote:
>Karl-
>..MoveFirst
>..Find "Field_1='abc'"
>if not .EOF Then
> .Find "Field_2='xyz'"
> if not .EOF Then
> 'Find is done
> end if
>end if
>
>-It is optimum
>________________________________
>Harvey Triana... psoft@latino.net.co
>Microsoft Developer MVP- Visual Basic
>www.eidos.es/VeXPERT
>
>"Karl Robinson" <kklo@hehe.com> escribió en el mensaje
>news:3b58e59d$1@news.devx.com...
>>
>> Hi,
>>
>> I try to use the FIND method of an ADO recordset object with this
>criteria,
>> Field_1='abc' AND Field_2='xyz'
>> It returns an error 3001.
>> In fact, on the form, users browse thousands of records from a MSFlexGrid
>> and they can input values for different fields everytime to find the
>record
>> they want. Thus, the criteria can be a combination of any fields of the
>> recordset. If record found, highlight the record in the MSFlexGrid.
>>
>> Can anyone give me some hits for doing this ?
>>
>> Thanks a lot.
>> Karl
>
>

[Harvey Triana]

Test it.

It's the same:

If exp1 And exp2 And exp3 Then
Call Tarea
End If

Shortcut:

If exp1 Then
If exp2 Then
If exp3 Then
Call Tarea
End If
End If
End If

________________________________
Harvey Triana... psoft@latino.net.co
Microsoft Developer MVP- Visual Basic
www.eidos.es/VeXPERT

"Arthur Wood" <wooda@saic-trsc.com> escribió en el mensaje
news:3b59ec65$1@news.devx.com...
>
> Harvey,
> That will only succeed in finding the first record which has Field_2 =
> 'xyz' and that may OR MORE LIKELY MAY NOT be a record which ALSO has
Field_1
> = 'abc'.
>
> The fact that the first FIND suceeded DOES NOT restrict the recordset to
> ONLY those records for whcih Field_1 has the value 'abc'. It simply moves
> the record pointer to the first record, in the ENTIRE recordset, that has
> Field_1 = 'abc'. The Second Find them simply moves the record pointer to
> the first record (in the same ENTIRE record set) that has Field_2 = 'xyz'.
>
> Arthur Wood
>
>
> "Harvey Triana" <psoft@latino.net.co> wrote:
> >Karl-
> >..MoveFirst
> >..Find "Field_1='abc'"
> >if not .EOF Then
> > .Find "Field_2='xyz'"
> > if not .EOF Then
> > 'Find is done
> > end if
> >end if
> >
> >-It is optimum
> >________________________________
> >Harvey Triana... psoft@latino.net.co
> >Microsoft Developer MVP- Visual Basic
> >www.eidos.es/VeXPERT
> >
> >"Karl Robinson" <kklo@hehe.com> escribió en el mensaje
> >news:3b58e59d$1@news.devx.com...
> >>
> >> Hi,
> >>
> >> I try to use the FIND method of an ADO recordset object with this
> >criteria,
> >> Field_1='abc' AND Field_2='xyz'
> >> It returns an error 3001.
> >> In fact, on the form, users browse thousands of records from a
MSFlexGrid
> >> and they can input values for different fields everytime to find the
> >record
> >> they want. Thus, the criteria can be a combination of any fields of
the
> >> recordset. If record found, highlight the record in the MSFlexGrid.
> >>
> >> Can anyone give me some hits for doing this ?
> >>
> >> Thanks a lot.
> >> Karl
> >
> >
>

[Arthur Wood]

Harvey,
Yes those two If..then expressions ARE the same, but that DOES NOT apply
to the Find Method of a Recordset.

Find will Find the first Record that matches the criterion. The Next Find
twill then serach the ENTIRE recordset (from the beginning) for the first
record that matches the new criterion (regardless of whether or not that
record might or might not match the first criterion). Find is NOT Cumulative
(that is the Fist find DOES NOT Continue to apply when you do a second find
with a different criterion).

Arthur Wood


"Harvey Triana" <psoft@latino.net.co> wrote:
>Test it.
>
>It's the same:
>
>If exp1 And exp2 And exp3 Then
>Call Tarea
>End If
>
>Shortcut:
>
>If exp1 Then
>If exp2 Then
>If exp3 Then
>Call Tarea
>End If
>End If
>End If
>
>________________________________
>Harvey Triana... psoft@latino.net.co
>Microsoft Developer MVP- Visual Basic
>www.eidos.es/VeXPERT
>
>"Arthur Wood" <wooda@saic-trsc.com> escribió en el mensaje
>news:3b59ec65$1@news.devx.com...
>>
>> Harvey,
>> That will only succeed in finding the first record which has Field_2
=
>> 'xyz' and that may OR MORE LIKELY MAY NOT be a record which ALSO has
>Field_1
>> = 'abc'.
>>
>> The fact that the first FIND suceeded DOES NOT restrict the recordset
to
>> ONLY those records for whcih Field_1 has the value 'abc'. It simply moves
>> the record pointer to the first record, in the ENTIRE recordset, that
has
>> Field_1 = 'abc'. The Second Find them simply moves the record pointer
to
>> the first record (in the same ENTIRE record set) that has Field_2 = 'xyz'.
>>
>> Arthur Wood
>>
>>
>> "Harvey Triana" <psoft@latino.net.co> wrote:
>> >Karl-
>> >..MoveFirst
>> >..Find "Field_1='abc'"
>> >if not .EOF Then
>> > .Find "Field_2='xyz'"
>> > if not .EOF Then
>> > 'Find is done
>> > end if
>> >end if
>> >
>> >-It is optimum
>> >________________________________
>> >Harvey Triana... psoft@latino.net.co
>> >Microsoft Developer MVP- Visual Basic
>> >www.eidos.es/VeXPERT
>> >
>> >"Karl Robinson" <kklo@hehe.com> escribió en el mensaje
>> >news:3b58e59d$1@news.devx.com...
>> >>
>> >> Hi,
>> >>
>> >> I try to use the FIND method of an ADO recordset object with this
>> >criteria,
>> >> Field_1='abc' AND Field_2='xyz'
>> >> It returns an error 3001.
>> >> In fact, on the form, users browse thousands of records from a
>MSFlexGrid
>> >> and they can input values for different fields everytime to find the
>> >record
>> >> they want. Thus, the criteria can be a combination of any fields of
>the
>> >> recordset. If record found, highlight the record in the MSFlexGrid.
>> >>
>> >> Can anyone give me some hits for doing this ?
>> >>
>> >> Thanks a lot.
>> >> Karl
>> >
>> >
>>
>
>

[Ron Phillips]

You might try using the Clone() method instead to see if you have
matches. You could use something like:

Dim rsFilter As ADODB.Recordset
rs.Filter = "Field_1 = 'abc' and Field_2 = 'xyz'"
Set rsFilter = rs.Clone
If Not (rsFilter.BOF And rsFilter.EOF) Then
'Records found
End If

The clone method basically filters the original by some criteria. I
don't know about the speed implications, but I'm assuming this is
going to be faster than executing another query.

Ron

On 20 Jul 2001 19:14:53 -0700, "Karl Robinson" <kklo@hehe.com> wrote:
>
>Hi,
>
>I try to use the FIND method of an ADO recordset object with this criteria,
>Field_1='abc' AND Field_2='xyz'
>It returns an error 3001.
>In fact, on the form, users browse thousands of records from a MSFlexGrid
>and they can input values for different fields everytime to find the record
>they want. Thus, the criteria can be a combination of any fields of the
>recordset. If record found, highlight the record in the MSFlexGrid.
>
>Can anyone give me some hits for doing this ?
>
>Thanks a lot.
>Karl

[marc]

I do not know if this was stated yet (I did not see it). The find method
of an ADO recordset does not support multiple criteria. You will have to
use another way. A select query that returns a unique identifier to use
with the find method is possibility.

marc

"Karl Robinson" <kklo@hehe.com> wrote:
>
>Hi,
>
>I try to use the FIND method of an ADO recordset object with this criteria,
>Field_1='abc' AND Field_2='xyz'
>It returns an error 3001.
>In fact, on the form, users browse thousands of records from a MSFlexGrid
>and they can input values for different fields everytime to find the record
>they want. Thus, the criteria can be a combination of any fields of the
>recordset. If record found, highlight the record in the MSFlexGrid.
>
>Can anyone give me some hits for doing this ?
>
>Thanks a lot.
>Karl