I'm pretty new to using queries. I'm currently trying to create a simple query for one table on one field. The field I want to query is a product number. I want to use a search that can pull the matches or close matches for the product number that is searched for. The product numbers have differing lengths, some with spaces, others with dashes or underscores and typically start and end with a letter.
As an example, here are a few product numbers used in the DB:
What wildcards would need to be used for the criteria? Would it need to be:
"?*?" used with the like operator?
I'm completely new to writing queries, so I apologize for my lack of understanding about queries and how they are built. What I'm trying to do is allow someone to type in a search string in a text field on a web form and search the database field for a match or the closest match to the search string that was typed in.
You mentioned that *? would retrieve everything where there is an entry.
How can I modify that to search for the closest matches or an exact match if it is in the database?
I don't know anything about web forms but assuming a query can be fed criteria from one then I think probably c* or a* etc.
The more letters before the asterisk the less records returned.
For example M* gets Mike, Mark, motor, master etc. But Mi* gets Mike, Michelle and Mitch but not motor, Mark or master.
But if the web form works like a form in Access then things will be different. A query can have its criteria established by reference back to entries ina form. But things like Mi* no longer work.
What you probably need is a drop down combo box list. They are very simple to make on an Access for but have no idea of what happens with a web form. The drop down list gives two options
1) You click the arrow and scroll through the listings and then click on the one you want and Access then takes you to the record in the form
2) You can start typing and Access will bring to the top the first thing that approximately matchs. The underlying list that supports the combo will normally be in ascending order sort. So if you type Mi then if Mike was in the list it would come to the top.....unless Michelle was also in the list and then it would come to the top.