?
Solved

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

Posted on 2013-01-24
14
Medium Priority
?
1,307 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
[X]
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
  • 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
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 1400 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 48

Assisted Solution

by:Dale Fye
Dale Fye earned 600 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 48

Assisted Solution

by:Dale Fye
Dale Fye earned 600 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 48

Expert Comment

by:Dale Fye
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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Suggested Courses

741 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