Solved

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

Posted on 2013-01-24
14
1,097 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Not long ago I saw a question in the VB Script forum that I thought would not take much time. You can read that question (Question ID  (http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/Q_28455246.html)28455246) Here (http…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

910 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

21 Experts available now in Live!

Get 1:1 Help Now