Using wildcards (Like operator) in a VBA parameter prompt.

I am trying to create a button that the user clicks, and is prompted for Equipment name (InvPCName).

I want them to be able to enter the actual name or wildcards (*part of name*) or part of name with no wild cards.

Once they click OK, I want a the same form to open with only the records that match the query.

Right now, a simple [Enter Equipment Name] in the WHERE condition works, but doesn't accept wildcards, only the exact name.

I would love for it to behave just like the "contains" function on the right-click menu in Access 2010, if possible.

Any suggestions?
TSUS MISAsked:
Who is Participating?
 
IrogSintaConnect With a Mentor Commented:
Actually our original answers only work inside a query.  To do this in VBA, you can use the InputBox function.  

Dim strCriteria As String
strCriteria = InputBox{"Enter Equipment Name")
DoCmd.OpenForm "frmInventory", , , "InvPCName Like  *" & strCriteria & "* "

Open in new window

0
 
IrogSintaCommented:
Change your criteria to:
Like "*" & [Enter Equipment Name] & "*"
0
 
mbizupCommented:
Have you tried using this for your criteria?

LIKE "*" &  [Enter Equipment Name] & "*"
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
TSUS MISAuthor Commented:
I have my code as this:

Private Sub FindByName_Click()

DoCmd.OpenForm "frmInventory", , , "InvPCName Like " * " & [Enter Equipment Name] & " * ""

End Sub

Open in new window


And I am getting a runtime error 13: Type mismatch.
0
 
IrogSintaCommented:
Make this correction:
DoCmd.OpenForm "frmInventory", , , "InvPCName Like  *" & [Enter Equipment Name] & "* "

Open in new window

0
 
TSUS MISAuthor Commented:
Now I'm getting:
Run-time error 2465:
Database can't find the field '|1' referred to in your expression.
0
 
Dale FyeConnect With a Mentor Commented:
Believe you need to wrap that in single or double quotes, like:
DoCmd.OpenForm "frmInventory", , , "InvPCName Like  '*" & strCriteria & "*'"

Open in new window

0
 
IrogSintaCommented:
Thanks for the correction, Dale.
0
 
Dale FyeConnect With a Mentor Commented:
Also need to replace:

strCriteria = InputBox{"Enter Equipment Name")

with:

strCriteria = InputBox("Enter Equipment Name")

replaced { with (
0
 
IrogSintaCommented:
See what I get for typing on a Bluetooth keyboard with no numbers row in the dark.
:-)
0
 
Dale FyeCommented:
@irog:  It's Ron, right?

I think it's worse with an iPad keyboard, where you have to shift to numbers, then to symbols.  I love to be able to browse and answer questions while on the move, but I wish I could create a signature box for EE that reads something like:

I apologize for typos, I typed on my iPad!

:-)
0
 
IrogSintaCommented:
I apologize for typos, I typed on my iPad!
Hilarious :-)

Ron
0
 
mbizupCommented:
I love it!

I post from my cellphone occasionally, using voice recognition.  EE's interface does funny things to those posts like deleting entire blocks of text, and of course sometimes what gets posted is not quite what you said,... it can be a real mess.
0
 
TSUS MISAuthor Commented:
Thanks, guys.
You're both lifesavers.
I hope you're ok with the divvying of the points.
0
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.