Microsoft KB Archive/209536

= ACC2000: How to Search for Characters or Strings in Records =

Article ID: 209536

Article Last Modified on 6/29/2004

-

APPLIES TO


 * Microsoft Access 2000 Standard Edition

-



This article was previously published under Q209536



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

This article applies only to a Microsoft Access database (.mdb).



SUMMARY
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
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:  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

</li> Clear the Show check box of that column.</li> Run the query by clicking Run on the Query menu. Note that you receive the following results:

*Pat

Pat*

Ri*ck

</li></ol>

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: <ol> Start Microsoft Access, open a database, and create a new table called Nametest .</li> Create a text field called FirstName and type the following data into the table:

FirstName


 * Pat

Pat*

Tom

Ri*ck

Harry

</li> Create a new query based on the Nametest table.</li> Drag the FirstName field from the field list box to the query grid.</li> 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.</li> Run the query. Note that you receive the following results:

*Pat

Pat*

Ri*ck

</li></ol>

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 [*]


 * 1) should be entered as [#]

? should be entered as [?]

[ should be entered as [[]

] should be entered as []]

! should be entered as [!]

- should be entered as [-]

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: <ol> Start Microsoft Access, open a database, and create a new table called Nametest .</li> Create a text field called FirstName and type the following data into the table:

FirstName


 * Pat

Pat*

Tom

Ri*ck

Harry

</li> On the Edit menu, click Find.</li> In the Find and Replace dialog box, under Find What, type [*], and then under Match select Any Part of Field.</li> Click the Find Next button. Note that you receive the following result:

*Pat

</li> Click the Find Next button again. Note that you receive the following result:

Pat*

</li> Click the Find Next button again. Note that you receive the following result:

Ri*ck

</li></ol>

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

Keywords: kbhowto kbusage KB209536

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.