Improve company productivity with a Business Account.Sign Up

x
?
Solved

VBA Syntax for wildcard filter

Posted on 2011-02-15
5
Medium Priority
?
2,599 Views
Last Modified: 2012-05-11
I use the following code under a button to filter a form based on the Business Name field:

Me.Filter = "[Business Name]='" & Me.Find & "'"
Me.FilterOn = True
Me.Requery

This works well for a one word business name such as "Acme"

I would like to change the filter to allow the use of the * wild card so I can find "Acme Inc." "Acme Corp" or "The Acme Company":

How do I change this syntax to filter by business name with the star wildcard before and after

Thank you in advance for your help.

Bob
0
Comment
Question by:Robert Wardlow
  • 2
  • 2
5 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 34897737
Me.Filter = "[Business Name] Like '" & Me.Find & "*'"
Me.FilterOn = True
Me.Requery
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 2000 total points
ID: 34897747
Me.Filter = "[Business Name] Like '*" & Me.Find & "*'"
Me.FilterOn = True
0
 
LVL 1

Author Comment

by:Robert Wardlow
ID: 34897939
Perfect. Thank you so much.
0
 
LVL 1

Author Closing Comment

by:Robert Wardlow
ID: 34897949
Great, thanks
0
 
LVL 61

Expert Comment

by:mbizup
ID: 34897973
I'd recommend using the LIKE operator without embedding the wildcards in the code as follows:

Me.Filter = "[Business Name] Like '" & Me.Find & "'"

This allows the user to specify any wildcard in the search text:

*ACME*  (* for multiple characters)
?ACME    (? for a single character)

or return exact search results by specifying no wildcards:

ACME   (will return exact name)
0

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Hi, this video explains a free download that you can incorporate into your Access databases, or use stand-alone for contact management. Contacts -- Names, Addresses, Phone Numbers, eMail Addresses, Websites, Lists, Projects, Notes, Attachments…

607 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