Results 1 to 4 of 4
  1. #1
    4 Star Lounger
    Join Date
    Apr 2001
    Location
    Guatemala City
    Posts
    515
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Concatenated find (Win ME/Access 97)

    I need to find a record using VBA based on two criteria, for example invoice number and item number. Can I do one "find" for invoice number and immediately follow it with another "find" based on item number? Can I combine the "find" operations some way?

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Concatenated find (Win ME/Access 97)

    You can create a criteria string for your Find that includes the AND operator, something like this:

    strCriteria = "[invoice number] = 123 and [item number] = 456"

    use the appropriate criteria stringin your Find method.
    Charlotte

  3. #3
    Lounger
    Join Date
    Jan 2001
    Location
    Pennsylvania, USA
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Concatenated find (Win ME/Access 97)

    It depends on how your data is organized. If you have invoices (with their invoice numbers) in one table and the items on each invoice in another then you may have to do two finds. If invoice number and item number are in the same table then one find will do:
    <font color=448800>
    Dim db As Database
    Dim recordset As Recordset

    Set db = Currentdb()
    Set recordset = db.OpenRecordset("invoice_item_table")
    recordset.FindFirst "invoicenbr = " & [Search Invoice Number] & " And itemnbr = " & [Search Item Number]
    </font color=448800>
    [Search Invoice Number] and [Search Item Number] are, of course, whatever fields are holding the invoice number and item number that you are searching for.

  4. #4
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Concatenated find (Win ME/Access 97)

    There would be no need to do 2 finds, apart from the fact of I wouldn't know how you would do this.

    What you would do with an Invoice table and an Item table is to join them (on presumably InvoiceNbr) and then do the Criteria as Charlotte suggests.
    Pat <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •