Solved

Using forms as criteria with wildcards

Posted on 2004-08-04
5
486 Views
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.
0
Comment
Question by:gregdachs
  • 3
5 Comments
 
LVL 26

Expert Comment

by:dannywareham
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...
0
 
LVL 14

Accepted Solution

by:
JohnK813 earned 100 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.
0
 

Author Comment

by:gregdachs
ID: 11719288
Perfect.  Thank you.
0
 
LVL 26

Expert Comment

by:dannywareham
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] & "'"
Else
    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

:-)
0
 
LVL 26

Expert Comment

by:dannywareham
ID: 11719329
Oops, beat me too it...
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

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…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
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…

840 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