Searching for records where the critera are in two fields

Posted on 2011-05-13
Last Modified: 2012-05-11
I am using Access 2007.

In an ideal world I would like to do the following Pseudo :-

If [Field A] = “text A” AND [Field B] = “text B” then [Field C] = “text C”

I have this bit of code in a sub:-

Art = “text A”
UnusedRecord = “text B”

Line1:            MyRecordset.FindFirst "[Field A] = ' " & Art & " ' "
Line2:            MyRecordset.FindNext "[Field B] = ' " & UnusedRecord & " ' "

Line1 will successfully find “text A” in [Field A] OK.
But Line2 will not find “text B” within the current record.
It always goes to the next record before it starts to search (As it happens “text B” may also be in [Field B] of the next record).

Microsoft say that FindNext starts at the CURRENT record  - but it doesn’t.
And I cannot force it to do so.

Is this a bug in Access 2007 VBA? Or maybe their description of this function is wrong!

How can I find a valid record where the criteria are located in two fields?
Question by:usatrfe
    LVL 39

    Expert Comment

    Why not to check [Field B] in record, where you are after firsf find?
    MyRecordset.FindFirst "[Field A] = ' " & Art & " ' "
    If MyRecordset![Field B] = ' " & UnusedRecord & " ' " then ....
    LVL 39

    Expert Comment

    And little comment - if findnext will start search from current record - you never find next, you will be always on same record
    LVL 92

    Accepted Solution

    Or just evaluate both columns in the same statement:

    MyRecordset.FindFirst "[Field A] = '" & Art & "' And [Field B] = '" & UnusedRecord & "'"

    Open in new window


    Author Comment

    Brilliant 100% worked!

    That don't let off MS for there poor descrition of FindNext though!!!

    Thanx mathewpatrick

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Top 6 Sources for Identifying Threat Actor TTPs

    Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

    Suggested Solutions

    In the article entitled Working with Objects – Part 1 (, you learned the basics of working with objects, properties, methods, and events. In Work…
    Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
    Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
    In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

    737 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

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now