Solved

Using forms as criteria with wildcards

Posted on 2004-08-04
5
485 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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

Suggested Solutions

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

776 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