Wildcard Query in VBA

I am attempting to use VBA to get a continuous form to open using a wildcard query. Specifically, I am trying to set things up so that the user inputs a search term (partial company name) into a textbox on a form and by clicking a "search" button opens a continuous form with matches to the search term (in any position). I have tried the following code which opens the form, but no search results appear:

DoCmd.OpenForm " CF_Companies", , , "CompanyName Like '*" & SearchTextBox & "*'"

I have also tried numerous variations of the above, but alas only get synthax errors. Can anyone help point me in the right direction ? Thank you !
Paul McCabeAsked:
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.

Do you get results if you open a new Query window and select the following in SQL:

SELECT * FROM CF_Companies
WHERE CompanyName Like '*XXX*'

where XXX is a typical search term.
If you don't get anything here, the problem is not your Open command.
Mike EghtebasDatabase and Application DeveloperCommented:
side note: If field [CompanyName] is indexed for faster search, then use of the first * will drastically slow down the search.

Paul McCabeAuthor Commented:
I tried the query suggested by rspahitz and it works perfectly (CF_Companies is a continuous form, so I ran the query against the underlying table).
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

Sorry...you're right...should have been against the table.

How about this as a test.  Open the form then put that query in the Record Source of the form.
If that works, it may want more...maybe you'll need to use SearchTextBox.Value (append ".Value") to the query since it may be getting confused with the reference.
Paul McCabeAuthor Commented:
Not sure if I understood this correctly. By "form" I take it you mean CF_Companies. The successful query I ran against the table was:
SELECT * FROM T_Companies WHERE CompanyName Like '*XXX*'
but putting this into the record source of CF_Companies leads to a syntax error....
OK, maybe the next step is to go into the VBA.
Do you know how to set breakpoints? If you set one on that line, check the value of "CompanyName Like '*" & SearchTextBox & "*'"

Alternately, on the line before you can put this:

Debug.Print ">CompanyName Like '*" & SearchTextBox & "*'<"

After it runs, open the immediate window (Alt+F11, I think) and see what shows to make sure it's what you expected.
Paul McCabeAuthor Commented:
I ran
Debug.Print ">CompanyName Like '*" & SearchTextBox & "*'<"
in the immediate window, which returned the result:
>CompanyName Like '**'<
Does this make sense ? I am afraid to say I am not familiar with breakpoints.
OK so it looks like it's searching for all records.  Is it showing that?
and if you enter something into the search box and run, you should get something else between the ** and an appropriate set of matching records.

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
I don't see anything wrong with the original expression.  Make sure that CompanyName is the actual name of the column.  HOWEVER, if you have made the mistake of using a table level lookup then this will not work because CompanyName might not actually be what you think it is.

Also, If you might have apostrophe's in names, then you have to use a different delimiter.

In a standard module, define a public constant.  It will make building strings easier.

Public Const QUOTE = """"

DoCmd.OpenForm " CF_Companies", , , "CompanyName Like " & QUOTE & "*" & SearchTextBox & "*" & QUOTE
Paul McCabeAuthor Commented:
To answer PatHartman's question, CompanyName is definitely the actual name of the relevant column (I have checked this at least 10 times !), and the continious form is based on a query, not a table. For reference, the text box on the main form into which the search term is input is not bound. I tried binding it but that didn't change anything. In end, I decided to try InputBox for inputting the search terms, and this is now working fine. So I will have users input search terms using InputBox. Your comments did'nt lead to a direct solution, but prompted me to try other things and find an acceptable solution. Thank you for your time and help !
I'm glad you got it working but there shouldn't be any difference between using a textbox on a form and an inputbox except that with the textbox on the form, you get more flexibility.
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.