• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 250
  • Last Modified:

Searching for records where the critera are in two fields

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?
  • 2
1 Solution
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 ....
And little comment - if findnext will start search from current record - you never find next, you will be always on same record
Patrick MatthewsCommented:
Or just evaluate both columns in the same statement:

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

Open in new window

usatrfeAuthor Commented:
Brilliant 100% worked!

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

Thanx mathewpatrick

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now