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
|