Business Solutions: Access: Wild Card Search - Find Anything
      By James D. Duncan, CPA  August 2006

     Queries in Access are usually looking for specific values or ranges. I have currently been working on searching for items that contain so set or subset of values. By using wild cards we can find a set of characters contained within a string.
    For example, let’s put some names in a table.

Now, Create a quick query using this table.

All we have done here is drag the Lastname and FirstName field to the grid. If we run this we will

 get a list of the last names and first names.
 

But what if we wanted all the LastNames that began with “B”. That is easy too. In the criteria of the LastName column enter like b and press enter. Access will format the critera to read “Like “b*”. The asterisk after the b is called a wildcard. This statement tell the field to display all the items that begin with b and ignore any other characters after that.

Run the data sheet view and we get a list of the Last names that begin with b.
 

Now if we wanted to search the LastName field for the characters “es” we just modify our search criteria to have the wild card “*” in the front and one in the back. The criteria would be “like *es*”.

In this case we get the same result. You will get a different set with real data. This example is fine for a couple of runs. Now if you want to get prompted for the characters you are looking at let’s change the criteria just a little.

Enter Like "*" & [Characters you want] & "*" into the criteria of LastName. Now you get a prompt and do not have to change the criteria for what you want. The prompt looks like this.

Enter the letter s and you get every LastName with an s in it.

Look in help for wild card and you will get a list of all the possibilities for searching using this method. Then just go WILD and create away. This then is incorporated into a form and used to populate a report and you have some real power and nice reports.

Get out there and put this to practice. Generate some errors, search the help for that little clue that corrects your code and makes your program run as you expected it too. This is United Way Fund drive time. I volunteer my time to a United Way Fund agency. They are doing exceptional work for the community. I feel humble to do a very minute part to give back to the community. When you help another you can hold your head up a little higher. You can affect how the community gets shaped.


 

James is a member of the CCOKC and an expert in Access and Excel. Click here to reach James