Help with syntax on wildcard search "If-Then" statement in VBA

Trying to write an IF-Then statement to search a specific field for records in a table.  I want the end user to be able to enter a value in a textbox (called "WCText").  And, I want the code to return to them all records that has that string somewhere in the field (field called "TIK_TITLE" in dataset tmp_RST).

So, in laymen's terms, end user enters the word "summary" (without the quotes) in the text box and I want to return all records that have this word somewhere in the field "TIK_TITLE".  This is what I have come up with so far:

If (UCase(tmp_RST![TIK_TITLE].value) Like ("*" & UCase(Me.WCText) & "*")) Then

I always get messed up with syntax for quotations.  This is not giving me any hard errors when I run it.  But, it is giving me records that do not have my search parameter in them?  

Can someone tell me if my syntax is wrong?  And, what should it be?  Thanks!
Who is Participating?
Using the LIKE operand and wild cards in a query would be faster.

In VBA, you would use the InStr() function to find a string inside another string.
Dale FyeCommented:
Assuming the textbox where you are entering the search criteria is in the form header or footer, you can simply set the forms Filter and FilterOn property, something like:

Private Sub txt_WCText_AfterUpdate

    me.Filter = "[TIK_TITLE] LIKE '*" & me.txt_WCText & "*'"
    me.FilterOn = True

End Sub
jmccloskAuthor Commented:
No, what I am doing is taking the textbox from an unbound form and running the string through a loop to check all records in a specific temporary table built with different sets of records each time.  So, the filter has to run in the code as an If statement that checks the records one at a time through the loop.  

I tried using a combination of single and double quotes as well as what I posted above.  But, VB didn't like any of the syntax I tried with single quotes.
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Dale FyeCommented:
So, you are looping through this set of records and doing something if a particular record meets this critiera?  Something like:

set rs = currentdb.openrecordset("SELECT * FROM tblX", dbOpendynaset)

While not rs.eof

    if UCASE(rs![TIK_TITLE]) LIKE "*" & UCASE(me.txt_WCText) & "*'" Then
       msgbox "meets criteria"
       msgbox "doesn't meet criteria"
    end if



Open in new window

Dale FyeCommented:
Could point Pat, using instr( ) would probably be more efficient, although Like does work in VBA.


if UCASE(rs![TIK_TITLE]) LIKE "*" & UCASE(me.txt_WCText) & "*'" Then


if instr(rs![TIK_TITLE], me.txt_WCText) > 0 then
jmccloskAuthor Commented:
Works perfect!  Thank you!  I split the solution... one for the idea, and the other for the exact syntax.  Thanks for your help!
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.