Find a specific record with two criteria

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Dear all,

I have a programming problem as below (only small part shown)

Set R1 = New ADODB.Recordset
R1.Open "ttEMP", D, adOpenKeyset, adLockPessimistic, adCmdTable

A = "LAB like '" + R2.Fields(0) + "'"
R1.Find (A)

This code is fine for one criteria, but if more than one criteria, it fails.
My code is below

A = "LAB like '" + R2.Fields(0) + "' and Name '" + R2.Fiels(1) + "'"

Any idea on this. Can 'Find' function use for more than one criteria search.
IF yes, what is the correct language as I cannot find it in the help.

Jack
 
The ADO Find method can only take a single parameter.

You can use a Filter on the recordset, or you can nest your Finds if the
recordset is ordered appropriately

.Find "fldLastName = 'Bishop'", , adSearchForward
If Not .EOF Then
.Find "fldFirstName = 'Norm'", , adSearchForward
If Not .EOF Then
msgbox "I found " & .Fields("fldFirstName") & " " &
..Fields("fldLastName")
Else
' do whatever ya want to do on not finding "Norm Bishop"
End If
End If


Note, though, that even if you could use multiple fields in the Find method,
you have a couple of things wrong with your code.

1) Name is a reserved word, and shouldn't be used as a field name. If you're
stuck with that name, put square brackets around it in the string: [Name]

2) You don't have an operator between Name and R2.Fiels(1) (i.e.: you need
=, Like or some other operator
 
We should also point out that & is the concatenation symbol. The + is a math
operator.

Douglas J. Steele said:
The ADO Find method can only take a single parameter.

You can use a Filter on the recordset, or you can nest your Finds if the
recordset is ordered appropriately

.Find "fldLastName = 'Bishop'", , adSearchForward
If Not .EOF Then
.Find "fldFirstName = 'Norm'", , adSearchForward
If Not .EOF Then
msgbox "I found " & .Fields("fldFirstName") & " " &
..Fields("fldLastName")
Else
' do whatever ya want to do on not finding "Norm Bishop"
End If
End If


Note, though, that even if you could use multiple fields in the Find method,
you have a couple of things wrong with your code.

1) Name is a reserved word, and shouldn't be used as a field name. If you're
stuck with that name, put square brackets around it in the string: [Name]

2) You don't have an operator between Name and R2.Fiels(1) (i.e.: you need
=, Like or some other operator


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



jack said:
Dear all,

I have a programming problem as below (only small part shown)

Set R1 = New ADODB.Recordset
R1.Open "ttEMP", D, adOpenKeyset, adLockPessimistic, adCmdTable

A = "LAB like '" + R2.Fields(0) + "'"
R1.Find (A)

This code is fine for one criteria, but if more than one criteria, it
fails.
My code is below

A = "LAB like '" + R2.Fields(0) + "' and Name '" + R2.Fiels(1) + "'"

Any idea on this. Can 'Find' function use for more than one criteria
search.
IF yes, what is the correct language as I cannot find it in the help.

Jack
Click to expand...
Click to expand...
 
Friends, thanks a lot.

Klatuu said:
We should also point out that & is the concatenation symbol. The + is a math
operator.

Douglas J. Steele said:
The ADO Find method can only take a single parameter.

You can use a Filter on the recordset, or you can nest your Finds if the
recordset is ordered appropriately

.Find "fldLastName = 'Bishop'", , adSearchForward
If Not .EOF Then
.Find "fldFirstName = 'Norm'", , adSearchForward
If Not .EOF Then
msgbox "I found " & .Fields("fldFirstName") & " " &
..Fields("fldLastName")
Else
' do whatever ya want to do on not finding "Norm Bishop"
End If
End If


Note, though, that even if you could use multiple fields in the Find method,
you have a couple of things wrong with your code.

1) Name is a reserved word, and shouldn't be used as a field name. If you're
stuck with that name, put square brackets around it in the string: [Name]

2) You don't have an operator between Name and R2.Fiels(1) (i.e.: you need
=, Like or some other operator


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



jack said:
Dear all,

I have a programming problem as below (only small part shown)

Set R1 = New ADODB.Recordset
R1.Open "ttEMP", D, adOpenKeyset, adLockPessimistic, adCmdTable

A = "LAB like '" + R2.Fields(0) + "'"
R1.Find (A)

This code is fine for one criteria, but if more than one criteria, it
fails.
My code is below

A = "LAB like '" + R2.Fields(0) + "' and Name '" + R2.Fiels(1) + "'"

Any idea on this. Can 'Find' function use for more than one criteria
search.
IF yes, what is the correct language as I cannot find it in the help.

Jack
Click to expand...
Click to expand...
Click to expand...
 
Back
Top