Find Method with Multiple Criteria


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 8 of 8

Thread: Find Method with Multiple Criteria

  1. #1
    Karl Robinson Guest

    Find Method with Multiple Criteria


    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

  2. #2
    Arthur Wood Guest

    Re: Find Method with Multiple Criteria


    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



  3. #3
    Harvey Triana Guest

    Re: Find Method with Multiple Criteria

    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




  4. #4
    Arthur Wood Guest

    Re: Find Method with Multiple Criteria


    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

    >
    >



  5. #5
    Harvey Triana Guest

    Re: Find Method with Multiple Criteria

    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

    > >
    > >

    >




  6. #6
    Arthur Wood Guest

    Re: Find Method with Multiple Criteria


    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
    >> >
    >> >

    >>

    >
    >



  7. #7
    Ron Phillips Guest

    Re: Find Method with Multiple Criteria


    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



  8. #8
    marc Guest

    Re: Find Method with Multiple Criteria


    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



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center