Solved

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

Posted on 2013-01-24
14
1,133 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

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.
Familiarize people with the process of utilizing SQL Server functions 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 Microsoft Ac…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

777 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