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?
usatrfeAsked:
Who is Participating?
 
Patrick MatthewsCommented:
Or just evaluate both columns in the same statement:

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

Open in new window

0
 
als315Commented:
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 ....
0
 
als315Commented:
And little comment - if findnext will start search from current record - you never find next, you will be always on same record
0
 
usatrfeAuthor Commented:
Brilliant 100% worked!

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

Thanx mathewpatrick
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.