Solved

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

Posted on 2013-01-24
14
1,067 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:TS Tech USA
  • 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:TS Tech USA
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
 
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:TS Tech USA
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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
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:TS Tech USA
ID: 38820807
Thanks, guys.
You're both lifesavers.
I hope you're ok with the divvying of the points.
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

This script will sweep a range of IP addresses (class c only, 255.255.255.0) and report to a log the version of office installed. What it does: 1.)      Creates log file in the directory the script is run from (if it doesn't already exist) 2.)      Sweep…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

758 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now