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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dale FyeOwner, Developing Solutions LLCCommented:
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.
Dale FyeOwner, Developing Solutions LLCCommented:
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

Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Dale FyeOwner, Developing Solutions LLCCommented:
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!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.