ACC2000: How to Search for Characters or Strings in Records (209536)

MORE INFORMATION

For this example, you create 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.

Method One: 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

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.

Method Two: 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 asterisks are wildcards, much like the Microsoft 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

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:

Method Three: 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 and Replace dialog box, under Find What, type [*], and then under Match select Any Part of Field.
5. Click the Find Next button. Note that you receive the following result:

   *Pat

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

   Pat*

7. 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 box 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 *#?[]!-.