The Recordset.Find method searches a Recordset for the FIRST record that satisfies a specified criterion. When run, the method scans each record until the criterion is met, and the recordset pointer stays on the found record; otherwise, the pointer ends up at EOF ( end of file ). Typical syntax is:

recordset.Find ( criteria, skipRows, searchDirection, start )

• criteria is the filter condition — a string containing a statement that specifies the "fieldName comparator value" to use in the search.
• skipRows is an optional integer whose default is zero, that specifies the offset from the current row or start bookmark to begin the search.
• searchDirection is an optional SearchDirectionEnum value that specifies whether the search should begin on the current row or the next available row in the direction of the search. Its value can be adSearchForward or adSearchBackward.
• start is an optional Variant bookmark to use as the starting position for the search.

The value in the criterion ( what to search for ) depends on the data type of the lookup field, which may be a string, floating point number, or date.

String values are delimited with single quotes ( for example, "state='CA'" ). Date values are delimited with the hash sign ( # ), for example, "start_date > #5/25/98#".

Searching for Records Using Recordset.Find Run Sample | View Source

If the comparison operator is LIKE, the string value may contain wild cards. Find accepts the asterisk ( * ), the percent sign ( % ), as well as the underscore ( _ ) as wild card characters.

Let's see a page using the Find method in action, this time using the FIRST letters of a field's value to search. Type any character( s ) to find a Product NAME in the Products table.

'open the products recordset
rsProds.Open "products", strProvider, 1, , adCmdTable

'find the record
rsProds.Find ( "ProductName LIKE '" & strSearch & "%'" )

Because here we essentially use strWhatever% as the given, a match only occurs when the lookup field contains the given string at the START of the field, and the record pointer stops at the record if found.

Note that Find will work only in a Recordset opened with a CursorType set to either adOpenKeyset ( 1 ) or adOpenDynamic ( 2 ).

Find is limited, however, to a single condition criterion, meaning one "fieldName comparator value" string to search. You cannot use AND or OR operators to expand the search criteria.

Depending on the need when using Find, the Recordset may have to be sorted on the lookup field by using an ORDER BY clause in the query that opens it,

strSQL="SELECT * FROM products ORDER BY ProductName"
rsProds.Open strSQL, strProvider, 1, , adCmdText

or by setting the Recordset's Sort property.

'set CursorLocation
rsProds.CursorLocation=adUseClient

'open the products recordset
rsProds.Open "products", strProvider, 1, 3, adCmdTable

'sort the recset
rsProds.Sort="ProductName"

'find the record
rsProds.Find ( "ProductName LIKE '" & strSearch & "%'" )

The ADO Sort property specifies one or more field names on which the Recordset is sorted, and whether each field is sorted in ascending or descending order.

Sort expects a string of comma-separated field names to sort on, where each name is a Field in the Recordset, optionally followed by a blank and the keyword ASC or DESC, to specify the field sort order. If not specified, the records are sorted in ascending order by default.

The data is not physically rearranged, but is simply accessed in the sorted order.

Note from the above that for Sort to work, the CursorLocation property is set to adUseClient ( 3 ) before the call to open the Recordset.

A temporary index will be created for each field specified in the Sort property if an index does not already exist.

Using Recordset.Sort with Recordset.Find Run Sample | View Source

Setting the Sort property to an empty string will reset the rows to their original order and delete temporary indexes. Existing database indexes will not be deleted, though.

The sample at right is essentially the same as the Find example above, but uses Recordset.Sort to set the order of the lookup field.

Well, that's about it for this session. Hopefully at least again, you have enhanced your knowledge of the different ways to let users search a database for information.

To search and return results, you can use SQL or the ADO Filter. To determine if a record exists, you can use the Find method.

Bear in mind that for Find to work, the Recordset's CursorType must be set to either adOpenKeyset ( 1 ) or adOpenDynamic ( 2 ). You can optimize performance by using Find in an ordered or sorted lookup field.

And for Sort to work, the Recordset's CursorLocation must be set to adUseClient ( 3 ).

Although certain data source providers include additional wild card characters, which you may include in applications using data sources specific to the provider, bear in mind that the inclusion of non-standard syntax will limit usage of the application to the actual ( or compliant ) provider.

While this may pose no problem in corporate intranet environments, this is a consideration when developing apps for the Web in general.

For example, when using wild cards to search for values in a Microsoft Access database using the MS OLE-DB Provider for Jet, you can include the following characters in queries and filter expressions.

For more information on string comparisons supported by the different providers, check the documentation for the particular data source. Better yet, always test your expressions against the specific provider you intend to use.

Though wild cards are meant to be used with text data types, you can sometimes use them successfully with other data types, such as dates, if you don't change the Regional Settings properties for these data types.

Next in this series:

• ADO Primer: Part IV
  • Letting users add, update, and delete information

If you haven't done so, I suggest you start with:

• ADO Primer: Part I
  • Basic database concepts
  • Connecting to a database
  • The Recordset object

ADO Primer: Letting Users Update Information