Solved Using Like and wildcards in a query (1 Viewer)

mistermarty

Member
Local time
Today, 14:09
Joined
Oct 31, 2020
Messages
41
Hi. I have a query set up so as when it runs it asks the user for an input, and the results are sorted depending on that input. So, if the user wants to see records that come under the code M05(1) or M06(2), they will type in that code and the records that come up are those allocated to M05(1) or M06(2) and so on. Is there a way to modify the query, so as they can just type in 05 and it brings up the records allocated to M05(1) and 06 for those in the M06(2) category etc? I have read some forums etc that mention using "Like" and the wildcards ? and *. Is it possible to include these in the Criteria row of the query in design view?
Thanks
😊
 

Minty

AWF VIP
Local time
Today, 14:09
Joined
Jul 26, 2013
Messages
10,354
If you use

Like "*" & [Enter your Search Text] & "*"

As the criteria, it will pull anything that matches.

You could do this from a form by using

Like "*" & Forms!YourFOrmName!YourControlName & "*"
 

mistermarty

Member
Local time
Today, 14:09
Joined
Oct 31, 2020
Messages
41
If you use

Like "*" & [Enter your Search Text] & "*"

As the criteria, it will pull anything that matches.

You could do this from a form by using

Like "*" & Forms!YourFOrmName!YourControlName & "*"
Perfect! Thanks so much for your help.
Marty (y) (y):)(y)(y)
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 10:09
Joined
Feb 19, 2002
Messages
42,970
If the code ALWAYS starts with "M" it is best to concatenate the M and use only the trailing *. Including the leading * will prevent the query engine from ever being able to use an index if there is one.

Like "M" & [Enter your Search Text] & "*"
 

mistermarty

Member
Local time
Today, 14:09
Joined
Oct 31, 2020
Messages
41
Thanks for your reply, Pat. The code starts with either M or H, and some have a number in brackets after, and some don't, e.g. H01, H07, M05(1), M20(7) and so on. I think that Minty's solution should work in my case.
Cheers
M🙂
 

Users who are viewing this thread

Top Bottom