Solved Using Like and wildcards in a query

[mistermarty]

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]

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]

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

 

[Pat Hartman]

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]

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