How to Use the LIKE operator?

[kcddoorman]

This is done in a DataAccessPage using the microsoft script editor with Access 2003.

Say I use this line to search a field for a matching string.

rs.find "[Customer NAME] = '" & CStr(InputBox("Please enter customer to find", "Find")) & "'"

That works fine if you know exactly what string you are looking for, but say you only know the first few letters. I tried to use the following line to get find to match wildcards without success. It will work if put the entire matching string in the input box; so "james" will match a recordset james, but the string "jam*s" will not match. Do I have a syntax problem or am I using the wrong function all together?

rs.find "[Customer NAME] LIKE '" & CStr(InputBox("Please enter customer to find", "Find")) & "'"

This is the entire block of code for the cmdSearch button onclick event.

<SCRIPT language=vbscript event=onclick for=cmdCustomerSearch>
<!--
' Clone the recordset.
 
Dim rs
Set rs = MSODSC.DataPages(0).Recordset.Clone
On error resume next
 
'rs.find "[CUSTOMER NAME]=" & cStr(InputBox("Please enter customer to find", "Find"))&""
rs.find "[Customer NAME] = '" & CStr(InputBox("Please enter customer to find", "Find")) & "'"
' error handling
 
If (err.number <> 0) Then
    Msgbox "Error: " & err.number & " " & err.description,,"Invalid Search"
    Exit Sub
End If
' Check search results for success.
If (rs.bof) or (rs.eof) Then
    Msgbox "No Product found",,"Search Done"
    Exit Sub
End If
MSODSC.DataPages(0).Recordset.Bookmark = rs.Bookmark
-->
</SCRIPT>

I've looked everywhere for this information but I can't find anything that deals with user inputted data to know if my syntax is right.

Thank you for any help you may have,
James

[FishVal]

Hi, James.

Try to enter '%' wildcard instead of '*'.

[kcddoorman]

I get the same problem. I'm thinking that the rs.find is only getting the string "jam*s" or "jam%s" and trying to match it. It doesn't see that the character % is a wildcard. the search works just fine when I type in "james".

Question. In this line:

rs.find "[job name] LIKE '" & CStr(InputBox("Please enter a job name to find", "Find")) & "'"

what does the ' character do. It appears after LIKE, like ' " &, and after the last & in between the quotes " ' "

I thought it was how you make a comment, but it is doing something else here.

[FishVal]

I get the same problem. I'm thinking that the rs.find is only getting the string "jam*s" or "jam%s" and trying to match it. It doesn't see that the character % is a wildcard. the search works just fine when I type in "james".

Question. In this line:

rs.find "[job name] LIKE '" & CStr(InputBox("Please enter a job name to find", "Find")) & "'"

what does the ' character do. It appears after LIKE, like ' " &, and after the last & in between the quotes " ' "

I thought it was how you make a comment, but it is doing something else here.

Weird. What is this recordset type? ADO? DAO?

[kcddoorman]

how do you tell? ADO or DAO I have used pearl to access the database but that doesn't mean anything I guess. ADO means access can access another databases right. I guess it is DAO because its been created and developed locally on one machine.

[FishVal]

how do you tell? ADO or DAO I have used pearl to access the database but that doesn't mean anything I guess. ADO means access can access another databases right. I guess it is DAO because its been created and developed locally on one machine.

Ok.

M$ says it is ADO.
Try to use Filter property instead of Find method.
BTW both wildcards work. ;)

[NeoPa]

James,

It looks to me like you're using LIKE but without ever putting in the wildcard character. Your code doesn't have either version in there.

rs.find "[job name] LIKE '" & CStr(InputBox("Please enter a job name to find", "Find")) & "'"

should be

rs.find "[job name] LIKE '" & CStr(InputBox("Please enter a job name to find", "Find")) & "&'"

Give me a sec & I'll dig up a link to an article that discusses them and how to use them.

[NeoPa]

Check out ANSI Standards in String Comparisons

[NeoPa]

Maybe I've misunderstood. It just occurred to me that you may be expecting the operator to enter the string with various wildcard characters embedded within it. In that case please ignore my earlier post.
It might be a good idea to use Debug.Print to show the actual line that's created though. This will help you determine exactly what is going wrong and where.

[kcddoorman]

rs.find "[job name] LIKE '" & CStr(InputBox("Please enter a job name to find", "Find")) & "&'"

This line of code would assume that the input search string will always have a wildcard after it. So a search for "John" will return not only John but also Johnson or Johnsonville or Johnny Number 5. Right? Or does it not matter.


Ok i just tried this in my code. What it does is append the & character to any string I enter into the textbox. So a search for James is really a search for James&. I'm beginning to think that FIND is not the way to go.

[kcddoorman]

This is very confusing. It seems like their should be a pre made function for this somewhere. OK. who is M$? is this l33t speak for something I am too noobish to know?

Let me clarify something here I did not realize until now.
This line

rs.find "[job name] LIKE '" & cstr(InputBox("Please enter a job name to find", "Find"))&"'"

will accept the wildcard character * only in certain conditions. Only if the search string is "Jame*" it will return a job name James. If the search string is "J*es" then I get no matching record. Maybe filtering the recordset is the way to go but I don't believe the salespeople will understand how to do it.

[Scott Price]

General Information :-)

M$ = Micro$oft (generally a sarcastic reference, eh?)

This link discusses single and double qoutes: Quotes (') and Double-Quotes (") and how to use them

Regards,
Scott

[NeoPa]

rs.find "[job name] LIKE '" & CStr(InputBox("Please enter a job name to find", "Find")) & "&'"

This line of code would assume that the input search string will always have a wildcard after it. So a search for "John" will return not only John but also Johnson or Johnsonville or Johnny Number 5. Right? Or does it not matter.


Ok i just tried this in my code. What it does is append the & character to any string I enter into the textbox. So a search for James is really a search for James&. I'm beginning to think that FIND is not the way to go.

My bad :(
That should have been

rs.find "[job name] LIKE '" & CStr(InputBox("Please enter a job name to find", "Find")) & "*'"

I was trying to illustrate putting the wildcard (*) character after the string entered.
I haven't tested this with the Find() function but I have with a SQL query where it works as claimed whether within, or at the end of, a comparison string.

[NeoPa]

...
Maybe filtering the recordset is the way to go but I don't believe the salespeople will understand how to do it.

Perhaps it would be better to code the filtering in for them. I never rely on operators to manage even walking and talking competently (well talking maybe ;D)

[Scott Price]

Perhaps it would be better to code the filtering in for them. I never rely on operators to manage even walking and talking competently (well talking maybe ;D)

How about breathing? :-) LOL

Regards,
Scott

[NeoPa]

That reminds me of how often my Yoga teacher has to remind the class to continue to breathe normally in the position :D
Pants all around at that point usually.

[kcddoorman]

Speaking of stretching out; I've been sitting at this computer for hours. I'm starting to feel like quazi modo.

[NeoPa]

Go stretch out.
Whatever the drive - always give yourself a physical break at frequent intervals (a couple of hours at most). It will help your brain work better as well as keep you physically healthier :)