ADO: .Find How to use the like operator correct?

Hi,

I use rst.Find to find records, but need to use the like operator.
And I need to use parameters for the fieldnames and searchstrings.
Anybody can give me a working example?

This doesn't work:

strFiendField = "CNR"
strSearchString = 100

varReturn = rsCustomer.Find(strFindField & " = " & Trim(strSearchString), , adSearchForward)

varReturn = rsCustomer.Find(strFindField & " LIKE " & Trim(strSearchString), , adSearchForward)


Franky

VB Code:

varReturn = rsCustomer.Find(strFindField & " = '" & Trim(strSearchString) & "'", , adSearchForward) 
 
varReturn = rsCustomer.Find(strFindField & " LIKE '" & Trim(strSearchString) & "%'", , adSearchForward)

thanks for your reply ....... but unfortunatly it give the same error message:

Compile Error: Expected Function or Variable


The Errorcode complain about the word FIND, but if I use it without all the parameters then it works.

rsCustomer.Find strFindField & " = '" & Trim(strSearchString) & "'"


Your code doesn't work unfortunatly ...... sniff

varReturn = rsCustomer.Find(strFindField & " = '" & Trim(strSearchString) & "'", , adSearchForward)


Any idea why?

Franky

Hi there Franky,

just to make sure I understand this,

you have a recordset rsCustomer, this recset is pulled from a table that have a field named CNR

you want to find the next record that has the value 100 or "100" ?

is this a numeric field or a text field ?

Try this:

VB Code:

rsCustomer.Find strFindField & " = '" & Trim(strSearchString) & "'"
 
varReturn = rsCustomer!CNR

I don't know if a variable can be subsituted for a field name in the second line, you may need the literal field.

Ok, I explain what I want to do.
I created a form in which the use can select a field that he want to search, he can select for search backward of forward and to search with the like operator or not. I want to use this parameters then to create the .find

I read in MSDn that it is possible to use .Find with the parameters as I want. Cowboys example works for sure, but how can I search then backwards? The Like Paramater works well if I don't use () for the .Find
Let's give a example:
I want to find Mr. Clinton and Mr. Clanton. I use now Cl?nton and the like, I want to search backwards.

About the question from peet about numeric fields and stringfields I am aware of it and will use it as needed.

I am happy that you try to help me, but it seems that until now nobody here used parameters for the ADO.find
If we find a solution for it then I will post the form here (because it is a very nice form) including the code, so that everybody can use a selfcreated .find


Thanks again and I wait for replys, hehe

Franky

rscomMyrecSet.Filter = MyFindField & " Like '*" & trim(strSearchstring) & "*'"

The stars act as wildcards so it's up to you to see whether you need wildcards in front or after your string

a comment to that... I think you have to use % when dealing with ado... I think the * will not work ...

Wally, I don't want to use a filter for my records. I want really to use the .Find

peet, I will keep it in mind with the %


..... but the question still remain the same .....


Franky

hehe... think I totally lost it now...

but hey.. I'm learning how to use Find :)

I created this little sample.
it works...

VB Code:

Private Sub Command1_Click()
    Dim cnn As New ADODB.Connection
    Dim rs As New ADODB.Recordset
    Dim SQL As String
    Dim iRes As Integer
    SQL = "Select * From Customer"
'    SQL = "Customer"
    
    cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=d:\test.mdb"
    rs.Open SQL, cnn, adOpenDynamic
    
    Do
        rs.Find "CNR = '100'", , adSearchForward
        If rs.EOF Then
            iRes = MsgBox("record not found Start from the beginning?", vbYesNo)
            If iRes = vbNo Then
                Exit Sub
            Else
                rs.MoveFirst
            End If
        Else
            MsgBox rs("Description")
            rs.MoveNext
        End If
    Loop
End Sub

Peet, it works :)

If you're going to look for values that answer to criteria then why not fill a recordset with an SQL-statement or filter an existing recordset with the criteria ?

It's pretty much the same result really. But if you insist on using find, perhaps the wildcards will work there also.

Try it out some time ;)

Quote:

---

Originally posted by Wally Pipp
Peet, it works :)

If you're going to look for values that answer to criteria then why not fill a recordset with an SQL-statement or filter an existing recordset with the criteria ?

It's pretty much the same result really. But if you insist on using find, perhaps the wildcards will work there also.

Try it out some time ;)

---

Hey Wally my favorite hare (*looks around hoping BonkerG does not see that* ):D

I found use of Find, when searching a list of items visible to the
user (grid) ...

I used some sql to show them a group of items. Some times
these groups are a bit big. then I use Find to make it possible for
the user to search in the group of items found. I think that makes
sense.

When one item found, it moves to that item in the list... if thats
not the item the user was looking for, he/she hits the find button
once more, and it moves to the next.

It makes it a better userinterface I think.

Only used this with DAO though... now also in ADO. the difference
is that FindNex, FindPrev have been removed form ADO :)

how about you Franky, did that sample I made shed any lights on
what you are trying to do ?

If not, you will have to elaborate even more :)

Also without having to go through all the streching, the Find method has a few options:

Quote:

---

Find (Criteria, SkipRows, SearchDirection, Start)

---

VB Code:

rsCustomer.Find strFindField & " = '" & Trim(strSearchString) & "'",,adSearchBackwards
 
varReturn = rsCustomer!CNR

MSDN ADO Programmer's Reference

I had nochance to try it, because I am since 2 days in Korea. When I am back home in Bangkok I willtry it, or I check it on my Computer. I will use for sure the Do ... loop because it makes my code shorter. I knew that there exist the parameters in find and exactly this was it what i tried to use.

As you said it is much better to use .Find as Filter or a SQl-statement in many situations because it most times the recordset is populated and the Find/Seek is only to jump to the corect record and not to make the selection smaller.

I will answer soon with my experience about it.

Thanks

Franky

I changed the way to the way that you showed here, cowboy.

The Find = is absolutly under control now. it workse perfect. What is still a problem is the LIKE. How to get a result with the like operator?

Franky

It's been shown already by bugman

VB Code:

rsCustomer.find strfindfield & " LIKE '%" & Trim(strSearchString) & "%'",,adSearchBackwards

explanation

LIKE 'F%' --> gets all records starting with F
LIKE '%F' --> ending with F
LIKE '%Finger%' --> with a Finger in the middle. A middle Finger.