I want a large group to be able to search within a query of names and experiences, to find all the names that have a common experience.
I can get the desired result by using the LIKE function in the criteria for experience (e.g., LIKE [Search Criteria]. The user then opens the query (or related form) and is first asked to enter the Search Criteria. For example, to find everyone who owns a Chevrolet, they can enter *Chev* or *Chevrolet*. (The database entry could be "Owns a Chevrolet" but the user may not know the exact wording, so the wild cards bring up the right answers.)
What I am trying to do is make this more user friendly, by allowing the user to enter the search criteria without the wildcard characters, and have the query reinterpret the user's entry to search for any appropriate response. In other words, to take a user entry of "Chev" and make it become "*Chev*".
I've tried adding a "Searchcriteria1" column in the query, which would be the user's input, and a "Searchcriteria2" column which would be ="*"&[Searchcriteria1]&"*". This puts the search in the right format, but I can't use Searchcriteria2 as the criteria for the query -- Access won't let me -- so this is probably not the correct approach.
How can I set up the query so that a user can select a specific search criteria and then have the query add the wild card characters so that the response includes all of the relevant records?