Solved

Using forms as criteria with wildcards

Posted on 2004-08-04
5
488 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
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…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

729 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