ACC: How to Search for Characters or Strings in Records

• Microsoft Access versions 1.0, 1.1, 2.0, 7.0, 97

SUMMARY

Novice: Requires knowledge of the user interface on single-user computers.

This article demonstrates three methods that you can use to search for and identify records containing a particular character or string. The example in this article searches for an asterisk (*) character.

MORE INFORMATION

Say, for example, that you have a table of names. Some of the names have an asterisk character embedded in them, and you would like a list of all the names containing asterisks.

First Method--Using the InStr() Function

One solution is to use the InStr() function. The InStr() function returns the position of the first occurrence of a string within another string. The example below uses the InStr() function to determine whether or not the asterisk character exists in the names in the table. If it does, it becomes a member of the query's recordset. To demonstrate this method, follow these steps:

1. Start Microsoft Access, open a database, and create a new table called Nametest.

2. Create a text field called FirstName and type the following data into the table:

         FirstName
         ---------
         *Pat
         Pat*
         Tom
         Ri*ck
         Harry
   

3. Create a new query based on the Nametest table.

4. Drag the FirstName field from the field list box to the query grid.

5. In the Field row of the query grid, next to the FirstName column, type the following expression:

         NewField: InStr(1,[FirstName],"*")
   

6. In the Criteria row of the same column, type:

         >0
   

7. Clear the Show check box of that column.

8. Run the query by clicking Run on the Query menu. Note that you receive the following results:

         *Pat
         Pat*
         Ri*ck
   

Second Method--Using the LIKE Operator

The LIKE operator returns a recordset that matches a pattern. The following example uses the LIKE operator to determine whether or not the asterisk character exists in the names in the table. If it does, it becomes a member of the query's recordset. To demonstrate this method, follow these steps:

1. Start Microsoft Access, open a database, and create a new table called Nametest.

2. Create a text field called FirstName and type the following data into the table:

         FirstName
         ---------
         *Pat
         Pat*
         Tom
         Ri*ck
         Harry
   

3. Create a new query based on the Nametest table.

4. Drag the FirstName field from the field list box to the query grid.

5. In the Criteria row of the same column, type:

         Like "*[*]*"
   

   The first and last * are wildcards, much like the MS-DOS asterisk wildcard. The [*] tells Microsoft Access to look for the character asterisk and not to use it as a wildcard.

6. Run the query. Note that you receive the following results:

         *Pat
         Pat*
         Ri*ck
   

If you are searching for a character that Microsoft Access interprets as a wildcard, such as *#?[]!-, it must be inside square brackets. For example:

   * should be entered as [*]
   # should be entered as [#]
   ? should be entered as [?]
   [ should be entered as [[]
   ] should be entered as []]
   ! should be entered as [!]
   - should be entered as [-]

Third Method--Using the Find Command

The Find command searches for the data you specify in the current table or recordset. The find command is available in the Datasheet view of a table, query, or form and in the Form view of a form. The Find dialog box remains open after each search. This enables you to find as many occurrences as you want without having to click Find repeatedly. This method finds each name with an asterisk one at a time. To demonstrate this method, follow these steps:

1. Start Microsoft Access, open a database, and create a new table called Nametest.

2. Create a text field called FirstName and type the following data into the table:

         FirstName
         ---------
         *Pat
         Pat*
         Tom
         Ri*ck
         Harry
   

3. On the Edit menu, click Find.

4. In the Find In Field: dialog box, under Find What, type [*], and then under Where: select Any Part Of Field.

5. Click the Find First button. Note that you receive the following result:

         *Pat
   

6. Click the Find Next button. Note that you receive the following result:

         Pat*
   

7. Click the Find Next button again. Note that you receive the following result:

         Ri*ck
   

Keywords          : QryOthr kbusage
Version           : 1.0 1.1 2.0 7.0 97
Platform          : WINDOWS
Hardware          : x86
Issue type        : kbhowto