Access has powerful tools to cull the information you need from your databases. In a table or form, you use the Find command or a filter. In a query, you enter expressions on the design grid to retrieve records from the underlying tables.

Often when we search for records, however, we’re not exactly sure what we’re looking for. For example, even after reading about the accounting firm Arthur Andersen for several months, many people still spelled Anders en as Anderson. But if you wanted Andersen and searched for Anderson, Access would not consider it a match; in other words, it would not find Andersen.

So how do you find values in a table, form, or query when you’re unsure about the exact spelling? You use wildcards.

For example, if you searched for Anders?n, Access would return all instances of both Anderson and Andersen, as well as any occurrence of Andersin or even Andersxn. That’s because you can use the question mark (?) wildcard to stand in for any alphanumeric character. Access would not find values such as Andersohn, however, because the question mark wildcard can substitute for only one character, not two or more.

Wildcards are useful not only for finding information when you don’t know an exact spelling (or an exact number), but also for finding a range of values. For example, suppose you were searching a field with three-character product codes for any code that begins with A and ends with J. You could search for A?J to find these codes.

There are other wildcards you can enter to satisfy your search requirements. Let’s take a look at using wildcards with the Find command in a form and then with expressions in queries.

 Use Wildcards With The Find Command

Open the Northwind.mdb database in the Samples subfolder of your Microsoft Office folder. Assuming you have made a copy (or have a backup) of the Northwind database, open the file. In the Database window, click the Forms button. Double-click the Products form to open it.

Suppose you were looking for any records for gnocchi, those delicious Italian dumplings. You know the word ends in chi and has noin it somewhere, but that’s the extent of your spelling guesswork. You can use the Find command with wildcards in your search term to locate it.

Click in the Product Name field of the first record. Click the binoculars icon on the Form View toolbar to open the Find And Replace dialog box. On the Find tab, click in the Find What box. Because you don’t know the number of letters preceding the no and chi, in your search term you’ll use the asterisk (*) wildcard, which can stand in for any number of alphanumeric characters. Type *no*chi.

The Look In field should read Product Name. Open the Match drop-down menu, choose Any Part Of Field, and click Find Next. In the Products form, Access jumps to Product ID 56, the record for Gnocchi di nonna Alice.

Click Find Next to see if there are any other values for this search term. Click OK in the message that tells you there aren’t any. Click Cancel in the Find And Replace dialog box and close the Products form.

 Use Wildcards In A Query

In the Database window, click the Queries button and click New. In the New Query dialog, with Design View selected, click OK. In the Show Table dialog box, double-click the Customers table to add it to the grid. Close the Show Table dialog box (click Close). Maximize the query window. In the Customers field list, double-click CompanyName, ContactName, ContactTitle, and Phone to add these fields to the design grid.

Suppose you wanted to create a query that showed all customers whose names begin with H, I, J, K, or L.You could use the brackets ([]) and hyphen wildcards to stand in for any of these letters.

On the Criteria row of the CompanyName field, type Like. This operator tells Access you’re searching for values that match the following expression. Next, tap the spacebar once and type "[h-l]*”. The [h-l] portion tells Access to search for any letter, H through L; the asterisk means, as we’ve seen, that any number of characters may follow. The entire expression you’ll type is Like "[h-l]*”. Click View to see all customers that begin with the letters H through L.

If you want to see records of customers whose first letters are not sequential, simply type each letter within the brackets. For example, to see records of companies that begin with letters H and Q, you would type the expression Like "[hq]*”.

 Use The Negative Wildcard

Finally, let’s say you want to see all customers whose names don’tbegin with letters H through L. You can use the exclamation point to retrieve these records. Click View. In the expression on the Criteria row of the CompanyName field, click before the H and type !. The expression you’ll want to type now is Like "[!h-l]*”. Click View. All of the records except those that begin with this range of letters are displayed. Close the query. If you decide to save it for future reference, be sure to give it a name, such as Smart Computing Wildcards, so you know it isn’t part of the original Northwind database.