Using forms as criteria with wildcards

Posted on 2004-08-04
Medium Priority
Last Modified: 2012-05-05
I am using a form to set the criteria for a form that opens from it.  the button executes the following code:

stDocName = "View_Item"
    stLinkCriteria = "[Equipment]=" & "'" & Me![Equipment] & "'"
    DoCmd.OpenForm stDocName, , , stLinkCriteria

I'd like to be able to put a wildcard character in the equipment field-- in other words, use 34* and get equipment #'s 345 and 348.  right now, if i use a * or anything else i can think of it just filters out everything.  

Thanks for any help.
Question by:gregdachs
  • 3
LVL 26

Expert Comment

ID: 11719159
I don't think that there's a simple way of doing this.

You'd have to use an if statement to check if the last character is "*", if so, treat as wildcard, if not, perform a normal search...
LVL 14

Accepted Solution

JohnK813 earned 400 total points
ID: 11719247
You could try changing your criteria string to

stLinkCriteria = "[Equipment] LIKE " & "'" & Me![Equipment] & "'"

That way, if a user enters 34, the criteria is "[Equipment] LIKE '34'", which should be the same as [Equiment]=34.
But, if the user enters 34*, LIKE would return 345 or 348 or even 34QWERTY

Correct me if I'm wrong here, Danny.

Author Comment

ID: 11719288
Perfect.  Thank you.
LVL 26

Expert Comment

ID: 11719325
I think that you're probably right - although LIKE can be a little tempremental.

You can try:

Dim sSQL as String
stDocName = "View_Item"

If right(me.equipment.value,1)="*" then
    sSQL = "[Equipment] LIKE " & "'" & Me![Equipment] & "'"
    sSQL = "[Equipment]=" & "'" & Me![Equipment] & "'"
End if

    stLinkCriteria = sSQL
    DoCmd.OpenForm stDocName, , , stLinkCriteria

This says to fetch the exact value matching [equipment] unless the last character is "*", in which case look for somethiing like the entry

LVL 26

Expert Comment

ID: 11719329
Oops, beat me too it...

Featured Post

Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

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

Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

624 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