![]() |
![]() |
|||||||||
|
Constant | Description |
adFilterNone |
Removes the current filter and restores all records to view. |
adFilterPendingRecords |
Enables you to view only records that have changed but have not yet been sent to the server. Only applicable for batch update mode. Not currently supported on UNIX. |
adFilterAffectedRecords |
Enables you to view only records affected by the last Delete, Resync, UpdateBatch, or CancelBatch call. Not currently supported on UNIX. |
adFilterFetchedRecords |
Enables you to view records in the current cache, that is, the results of the last call to retrieve records from the database. Not currently supported on UNIX. |
Remarks
Use the Filter property to selectively screen out records in a Recordset object. The filtered Recordset becomes the current cursor. This affects other properties such as AbsolutePosition, AbsolutePage, RecordCount, and PageCount that return values based on the current cursor, since setting the Filter property to a specific value will move the current record to the first record that satisfies the new value.(LastName = 'Smith' OR LastName = 'Jones') AND FirstName = 'John'
Instead, you would construct this filter as(LastName = 'Smith' AND FirstName = 'John') OR
(LastName = 'Jones' AND FirstName = 'John')
In a LIKE clause, you can use a wildcard at the beginning and end of the pattern (for example, LastName Like '*mit*'), or only at the end of the pattern (for example, LastName Like 'Smit*').Example
This Visual Basic example uses the Filter property to open a new Recordset based on a specified condition applied to an existing Recordset. It uses the RecordCount property to show the number of records in the two Recordsets. The FilterField function is required for this procedure to run.Public Sub FilterX()
Dim rstPublishers As ADODB.Recordset
Dim rstPublishersCountry As ADODB.Recordset
Dim strCnn As String
Dim intPublisherCount As Integer
Dim strCountry As String
Dim strMessage As String
` Open recordset with data from Publishers table.
strCnn = "driver={SQL Server};server=srv;" & _
"uid=sa;pwd=;database=pubs"
Set rstPublishers = New ADODB.Recordset
rstPublishers.CursorType = adOpenStatic
rstPublishers.Open "publishers", strCnn, , , adCmdTable
` Populate the Recordset.
intPublisherCount = rstPublishers.RecordCount
` Get user input.
strCountry = Trim(InputBox( _
"Enter a country to filter on:"))
If strCountry <> "" Then
` Open a filtered Recordset object.
Set rstPublishersCountry = _
FilterField(rstPublishers, "Country", strCountry)
If rstPublishersCountry.RecordCount = 0 Then
MsgBox "No publishers from that country."
Else
` Print number of records for the original
` Recordset object and the filtered Recordset
` object.
strMessage = "Orders in original recordset: " & _
vbCr & intPublisherCount & vbCr & _
"Orders in filtered recordset (Country = '" & _
strCountry & "'): " & vbCr & _
rstPublishersCountry.RecordCount
MsgBox strMessage
End If
rstPublishersCountry.Close
End If
End Sub
Public Function FilterField(rstTemp As ADODB.Recordset, _
strField As String, strFilter As String) As ADODB.Recordset
` Set a filter on the specified Recordset object and then
` open a new Recordset object.
rstTemp.Filter = strField & " = '" & strFilter & "'"
Set FilterField = rstTemp
End Function
Note:
When you know the data you want to select, it's usually more efficient to open a Recordset with an SQL statement. This example shows how you can create just one Recordset and obtain records from a particular country.
Public Sub FilterX2()
Dim rstPublishers As ADODB.Recordset
Dim strCnn As String
` Open recordset with data from Publishers table.
strCnn = "driver={SQL Server};server=srv;" & _
"uid=sa;pwd=;database=pubs"
Set rstPublishers = New ADODB.Recordset
rstPublishers.CursorType = adOpenStatic
rstPublishers.Open "SELECT * FROM publishers " & _
"WHERE Country = 'USA'", strCnn, , , adCmdText
` Print current data in recordset.
rstPublishers.MoveFirst
Do While Not rstPublishers.EOF
Debug.Print rstPublishers!pub_name & ", " & _
rstPublishers!country
rstPublishers.MoveNext
Loop
rstPublishers.Close
End Sub
Copyright © 2000 Chili!Soft