There is one other way to search for a specified string in a data set.
As in Part II: Selecting information from a database, the ADO Recordset.Filter can be used to selectively "screen out" records in a Recordset object.
The code is basically identical, except that the method applies a "filter condition" to the Recordset to retrieve the search results.
rsProds.Filter="ProductDescription LIKE '*" & strSearch & "*'"
Filter accepts both the asterisk ( * ) and the percent sign ( % ) as wild cards.
In the same way as in a query, you can include as many conditions as needed in a filter, connecting them with the AND or OR operator.
Again, for Filter to work, the Recordset must already be open.
The basic similarity between select queries and filters is that they both retrieve a subset of records from an underlying table or view. How you want to use the records that are returned determines whether you use a filter or a query.
Generally, use a filter to temporarily view or edit a subset of records. Use a query if you want to do any or all of the following:
- View the subset of records without first opening a specific table or form.
- Choose the tables containing the records you want to work with and add more tables at a later time if needed.
- Control which fields from the subset of records display in the results.
- Perform calculations on values in fields.
The following table summarizes the similarities and differences between queries and filters.
| Characteristics |
Filters |
Queries |
| Enable you to add more tables if you want to include their records in the subset that's returned |
No |
Yes |
| Enable you to specify which fields you want to display in the results from the subset of records |
No |
Yes |
| Can be used on a closed table |
No |
Yes |
| Produce results that can be used as the source of data for a form or report |
Yes |
Yes |
| Can calculate sums, averages, counts, and other types of totals |
No |
Yes |
| Can sort records |
Yes |
Yes |
| Enable you to edit data if editing is otherwise allowed |
Yes |
Yes, depending on the type of query |
In the next section, we look at another way to determine if a certain record exists, without necessarily displaying the search result.
