Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

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

Posted on 2013-01-24
14
1,152 Views
Last Modified: 2013-01-25
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?
0
Comment
Question by:Clinton Smith
  • 6
  • 3
  • 3
  • +1
14 Comments
 
LVL 29

Expert Comment

by:IrogSinta
ID: 38817220
Change your criteria to:
Like "*" & [Enter Equipment Name] & "*"
0
 
LVL 61

Expert Comment

by:mbizup
ID: 38817224
Have you tried using this for your criteria?

LIKE "*" &  [Enter Equipment Name] & "*"
0
 

Author Comment

by:Clinton Smith
ID: 38817505
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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
LVL 29

Expert Comment

by:IrogSinta
ID: 38817632
Make this correction:
DoCmd.OpenForm "frmInventory", , , "InvPCName Like  *" & [Enter Equipment Name] & "* "

Open in new window

0
 

Author Comment

by:Clinton Smith
ID: 38817663
Now I'm getting:
Run-time error 2465:
Database can't find the field '|1' referred to in your expression.
0
 
LVL 29

Accepted Solution

by:
IrogSinta earned 350 total points
ID: 38817684
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
 
LVL 47

Assisted Solution

by:Dale Fye (Access MVP)
Dale Fye (Access MVP) earned 150 total points
ID: 38818359
Believe you need to wrap that in single or double quotes, like:
DoCmd.OpenForm "frmInventory", , , "InvPCName Like  '*" & strCriteria & "*'"

Open in new window

0
 
LVL 29

Expert Comment

by:IrogSinta
ID: 38818541
Thanks for the correction, Dale.
0
 
LVL 47

Assisted Solution

by:Dale Fye (Access MVP)
Dale Fye (Access MVP) earned 150 total points
ID: 38818550
Also need to replace:

strCriteria = InputBox{"Enter Equipment Name")

with:

strCriteria = InputBox("Enter Equipment Name")

replaced { with (
0
 
LVL 29

Expert Comment

by:IrogSinta
ID: 38818592
See what I get for typing on a Bluetooth keyboard with no numbers row in the dark.
:-)
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 38818615
@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
 
LVL 29

Expert Comment

by:IrogSinta
ID: 38818644
I apologize for typos, I typed on my iPad!
Hilarious :-)

Ron
0
 
LVL 61

Expert Comment

by:mbizup
ID: 38818685
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
 

Author Closing Comment

by:Clinton Smith
ID: 38820807
Thanks, guys.
You're both lifesavers.
I hope you're ok with the divvying of the points.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

808 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question