ACC: How to Search for Characters or Strings in Records

ID: Q100135

The information in this article applies to:

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 an asterisk.

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:

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

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


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

Create a new query based on the Nametest table.

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

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

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

In the Criteria row of the same column, type:

>0

Clear the Show check box of that column.

Run the query by clicking Run on the Query menu. Note that you receive the following results:
```
      *Pat
      Pat*
      Ri*ck 
```

You can use this method to obtain a recordset containing any sequence of characters. To do so, just change the asterisk in the expression above to match the string or character for which you want to search.

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:

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

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


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

Create a new query based on the Nametest table.

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

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.

Run the query. Note that you receive the following results:
```
      *Pat
      Pat*
      Ri*ck 
```

This is another method that you can use to obtain a recordset containing any sequence of characters. To do so, just change the [*] in the criteria in step 5 to match the string or character for which you want to search.

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:

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

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


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

On the Edit menu, click Find.

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

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

*Pat

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

Pat*

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

Ri*ck

You can also use this method to find any sequence of characters. To do so, just change the [*] in the Find What: to match the string or character for which you want to search. Please see method two regarding searching for a character that Microsoft Access interprets as a wildcard, such as *#?[]!-.


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

Last Reviewed: March 24, 1999