October 1999

Search for Strings in a Text Field

by Susan Harkins

Access offers two features for locating a string. You can use the Find feature to highlight each occurrence of a string, one at a time, or you can run a select query, which displays a complete recordset of all the entries that contain the string you specify. In this article, we'll show you a few ways to locate strings using the select query approach.

Searching for an exact match

The simplest search looks for an exact match. By exact, we mean that the search and entry strings are identical. For instance, to search a field named Comments for the string Access, you'd enter the expression "Access" in that field's Criteria cell, as shown in Figure A. The resulting recordset would display those entries that contain only the string Access in the Comments field.

Figure A: The simple search expression "Access" matches entries that contain only the string Access.
[ Figure A ]

Searching for different strings

There may be times when you need to search for multiple exact matches. You have three options for doing so:

Use the In clause
Use the Or operator
Use the QBE grid's Or cell

To search for different strings using the In clause, use the form

In("string1","string2","string3")

where string1, string2, and string3 represent the strings you want to find. Access will return any entry that exactly matches one of the listed strings. For instance, to find the string Access or Excel, you'd use the expression

In("Access","Excel")

as shown in Figure B. This expression displays a recordset of all the entries that contain only the string Access or Excel in the Comments field. They're still exact matches--you've just added to the number of possible matches.

Figure B: Use the In clause to find more than one string.
[ Figure B ]

You can also use the Or operator to search for multiple strings. The form of such an expression would be

"string1" Or "string2" Or "string3"

where string1, string2, and string3 represent the strings you want to find. For instance, to find the strings Access and Excel, you'd use the expression

"Access" Or "Excel"

as shown in Figure C.

Figure C: The Or operator also lets you search for multiple strings.
[ Figure C ]

The last method takes advantage of the QBE grid's Or cell. Simply enter one string in the Criteria cell and the second in the Or cell, as shown in Figure D. Each cell below the Or cell is also an Or cell, so you can search for more than two strings.

Figure D: A third way to search for multiple strings is to use the QBE grid's Or cell.
[ Figure D ]

Searching for a string within a string

The previous solutions find exact matches. But what if you want to locate only a portion of an entry? Specifically, suppose you want to find one string within another. To do so, you simply include the asterisk (*) wildcard character in your search expression in the form

"*string*"

where string is the string you want to find. (Although there are other wildcard characters at your disposal, you'll probably use the asterisk character most often because it represents either 0 characters or any number of characters in its position.) Let's suppose you want to find all entries that contain the string Access, not just those entries that consist solely of the string Access. In this case, you'd use the expression

Like "*Access*"

as shown in Figure E.

Figure E: The * wildcard allows you to expand your search.
[ Figure E ]

(Access will enter the Like clause for you.) The resulting recordset will display exact matches and pattern matches. By pattern, we mean that the entry will contain the search string but not exclusively, as shown in Figure F.

Figure F: Our wildcard search matches entries that contain more than just the search string.
[ Figure F ]

Now, let's go one step further. Suppose you want to perform a pattern search for different strings. For this type of search, you need to use the Like clause in the form

Like "*string1*" Or Like "*string2*"

For instance, to find both Access and Excel, you'd use the expression

Like "*Access*" Or Like "*Excel*"

as shown in Figure G.

Figure G: You can search for multiple strings using the Like clause.
[ Figure G ]

This expression will match any entry that contains either string, including exact matches, as shown in Figure H.

Figure H: Our search expression matches all records that contain either string, including exact matches.
[ Figure H ]

You can also use the Or cell, as Figure I shows.

Figure I: You can use the QBE grid's Or cell to find multiple pattern matches.
[ Figure I ]

Excluding strings from a search

Thus far, all of our examples have dealt with finding specific strings. You may be wondering at this point how you can find entries that don't contain a specific string. Perhaps the easiest way to do this is to add the Not operator to your search expression. The rules for using the Not operator in exact and pattern searches are the same. For instance, to find the entries that don't equal the string Access, you'd use the expression

Not Like "Access"

The resulting recordset will display entries that contain the search string Access, but it won't include any exact matches--those entries that consist only of the search string. To exclude entries that consist of or contain the string Access, you'd use the expression

Not Like "*Access*"

The only difference between the two is the inclusion of the asterisk wildcards in the latter example. To exclude both Access and Excel from your search, you'd use the expression

Not Like "Access" And Not Like "Excel"

which will exclude only exact matches. To exclude entries that consist of or contain either string, you'd use the expression

Not Like "*Access*" And Not Like "*Excel*"

Again, the only difference between the two expressions is the inclusion of the asterisk characters. (You can't use the Or operator or the Or cell to exclude different strings.)

Conclusion

You can use a select query to display a recordset of entries that match a search expression. You can also exclude entries that match a search expression. In this article, we showed you how to perform both types of searches for specific strings.

Copyright © 1999, ZD Inc. All rights reserved. ZD Journals and the ZD Journals logo are trademarks of ZD Inc. Reproduction in whole or in part in any form or medium without express written permission of ZD Inc. is prohibited. All other product names and logos are trademarks or registered trademarks of their respective owners.