Select the product you need help with
- Internet Explorer
- Windows Phone
- More products
ACC2000: How to Search for Characters or Strings in Records
Article ID: 209536 - View products that this article applies to.
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).
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.
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() FunctionOne 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:
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:
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 [-]
Method Three: Using the Find CommandThe 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: