FIX: Compound Filter Condition in ADO2.5 Incorrectly Returns 0 Records (264002)


The information in this article applies to:

  • ActiveX Data Objects (ADO) 2.5
This article was previously published under Q264002

SYMPTOMS

Under all prior versions of ActiveX Data Objects (ADO), using a compound filter condition with expressions joined by an AND clause returned the correct subset of records. In ADO 2.5, there are a number of circumstances where a filter condition incorrectly returns 0 records.

RESOLUTION

This problem has been fixed in MDAC 2.6. For previous versions of MDAC the following workarounds are available:
  1. Build a local index on the second and subsequent fields in the filter condition. For example, to filter on the expression 
    FIELD1 = 'Value1' and FIELD2 = 'Value2'
    add the following line to your code before the filter condition is applied:
    rs.Fields("FIELD2").Properties("Optimize") = True
    Concatenate the filter condition to itself with the OR clause. For example, to filter on the expression 
    FIELD1 = 'Value1' and FIELD2 = 'Value2'
    add the lines:
    FilterString = "(FIELD1 = 'Value1' and FIELD2 = 'Value2')"
FilterString = FilterString & " OR " & FilterString
rs.Filter = FilterString
    If all the filter expressions test equality to a character expression, use LIKE in the filter expression. In place of 
    FilterString = "FIELD1 = 'Value1' and FIELD2 = 'Value2'
    substitute:
    FilterString = "FIELD1 LIKE 'Value1%' and FIELD2 LIKE 'Bar%"
  2. Concatenate the filter condition to itself with the OR clause. For example, to filter on the expression 
    FIELD1 = 'Value1' and FIELD2 = 'Value2'
    add the lines:
    FilterString = "(FIELD1 = 'Value1' and FIELD2 = 'Value2')"
FilterString = FilterString & " OR " & FilterString
rs.Filter = FilterString
    If all the filter expressions test equality to a character expression, use LIKE in the filter expression. In place of 
    FilterString = "FIELD1 = 'Value1' and FIELD2 = 'Value2'
    substitute:
    FilterString = "FIELD1 LIKE 'Value1%' and FIELD2 LIKE 'Bar%"
  3. If all the filter expressions test equality to a character expression, use LIKE in the filter expression. In place of 
    FilterString = "FIELD1 = 'Value1' and FIELD2 = 'Value2'
    substitute:
    FilterString = "FIELD1 LIKE 'Value1%' and FIELD2 LIKE 'Bar%"

STATUS

Microsoft has confirmed that this is a bug in the Microsoft products that are listed at the beginning of this article.

MORE INFORMATION

Due to the general nature of the problem, two reproduction scenarios are included here.
  1. In the simplest case, an in-memory recordset, the .Sort method must first be applied to the recordset in order to produce the anomaly. Add a reference to ADO 2.5 in the project that contains this code.

    The following sample program writes five rows to the in-memory recordset, sorts it, and applies a filter that should return one row:
    Dim rs As New ADODB.Recordset
Dim filterstring As String

With rs
    .Fields.Append "Field1", adInteger
    .Fields.Append "Field2", adInteger
    .Open

' write 5 records
    For i = 1 To 5
        .AddNew Array("Field1", "Field2"), Array(i, i)
    Next
    .Sort = "Field1 ASC"

' Workaround 1 - uncomment this line to create a local index on the second and subsequent condition
'.Fields("Field2").Properties("Optimize") = True

    filterstring = "(Field1 = 1 and Field2 = 1)"

' Workaround 2 - uncomment this line to OR the filterstring with itself
'    filterstring = filterstring & " OR " & filterstring

    rs.Filter = filterstring
    Debug.Print "I should have 1 record, I actually have: " & rs.RecordCount
End With

rs.Close
    In the second scenario, a hierarchical recordset is created from the sample NWIND Employees and Orders tables. Modify the pointer to NWIND.MDB in the connection string as appropriate. Also, add a reference to ADO 2.5 in the project that contains this code.

    For convenience, a filter is applied to the Employees table to display only one parent record.

    A compound filter is applied to the child recordset that should return nine records; instead, it returns zero (0) records. This is indicated by the value for "Child record count" that appears in the debug window.
    Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim rsCh As ADODB.Recordset
Dim i, j As Integer
Dim FilterString As String

' Change the pointer to NWIND in the connection string
With cn
    .ConnectionString = "Provider=MSDATASHAPE;" & _
        "Data Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=NWIND.MDB"
    .Open
End With

With rs
    .Open "SHAPE {SELECT EmployeeID FROM Employees} AS Employees " & _
        "APPEND ({SELECT EmployeeID, ShipCountry FROM Orders} AS Orders " & _
        "RELATE EmployeeID TO EmployeeID)", _
        cn, adOpenStatic, adLockOptimistic
End With

' print the parent and child field names
For i = 0 To rs.Fields.Count - 1
    Debug.Print rs.Fields(i).Name,
    If rs.Fields(i).Type = adChapter Then
        Set rsCh = rs.Fields("Orders").Value
        For j = 0 To rsCh.Fields.Count - 1
            Debug.Print rsCh.Fields(j).Name,
        Next
    End If
Next
Debug.Print

' print the data
rs.MoveFirst
' filter the parent rs so that only EmpID 1 is displayed
rs.Filter = "EmployeeID = 1"
Do While Not rs.EOF
    Debug.Print rs.Fields("EmployeeID")
    Set rsCh = rs.Fields("Orders").Value

' workaround 1 - build a local index on the second and subsequent
' columns returned
'rsCh.Fields("ShipCountry").Properties("Optimize") = True

    FilterString = "(EmployeeID = 1 and ShipCountry = 'France')"
' workaround 2 - concatenate the
' same expression twice using OR - uncomment to apply this workaround
'    FilterString = FilterString &" OR "& FilterString
    rsCh.Filter = FilterString

' this should return 9 records
   Debug.Print "Child record count: " & rsCh.RecordCount
   Do While Not rsCh.EOF
      Debug.Print "        " & rsCh.Fields("EmployeeID").Value, _
          rsCh.Fields("ShipCountry").Value
       rsCh.MoveNext
   Loop
Debug.Print
Loop
  2. In the second scenario, a hierarchical recordset is created from the sample NWIND Employees and Orders tables. Modify the pointer to NWIND.MDB in the connection string as appropriate. Also, add a reference to ADO 2.5 in the project that contains this code.

    For convenience, a filter is applied to the Employees table to display only one parent record.

    A compound filter is applied to the child recordset that should return nine records; instead, it returns zero (0) records. This is indicated by the value for "Child record count" that appears in the debug window.
    Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim rsCh As ADODB.Recordset
Dim i, j As Integer
Dim FilterString As String

' Change the pointer to NWIND in the connection string
With cn
    .ConnectionString = "Provider=MSDATASHAPE;" & _
        "Data Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=NWIND.MDB"
    .Open
End With

With rs
    .Open "SHAPE {SELECT EmployeeID FROM Employees} AS Employees " & _
        "APPEND ({SELECT EmployeeID, ShipCountry FROM Orders} AS Orders " & _
        "RELATE EmployeeID TO EmployeeID)", _
        cn, adOpenStatic, adLockOptimistic
End With

' print the parent and child field names
For i = 0 To rs.Fields.Count - 1
    Debug.Print rs.Fields(i).Name,
    If rs.Fields(i).Type = adChapter Then
        Set rsCh = rs.Fields("Orders").Value
        For j = 0 To rsCh.Fields.Count - 1
            Debug.Print rsCh.Fields(j).Name,
        Next
    End If
Next
Debug.Print

' print the data
rs.MoveFirst
' filter the parent rs so that only EmpID 1 is displayed
rs.Filter = "EmployeeID = 1"
Do While Not rs.EOF
    Debug.Print rs.Fields("EmployeeID")
    Set rsCh = rs.Fields("Orders").Value

' workaround 1 - build a local index on the second and subsequent
' columns returned
'rsCh.Fields("ShipCountry").Properties("Optimize") = True

    FilterString = "(EmployeeID = 1 and ShipCountry = 'France')"
' workaround 2 - concatenate the
' same expression twice using OR - uncomment to apply this workaround
'    FilterString = FilterString &" OR "& FilterString
    rsCh.Filter = FilterString

' this should return 9 records
   Debug.Print "Child record count: " & rsCh.RecordCount
   Do While Not rsCh.EOF
      Debug.Print "        " & rsCh.Fields("EmployeeID").Value, _
          rsCh.Fields("ShipCountry").Value
       rsCh.MoveNext
   Loop
Debug.Print
Loop
Modification Type:MajorLast Reviewed:10/15/2002
Keywords:kbBug kbDSupport kbMDAC260fix KB264002
©2002 Microsoft Corporation. All rights reserved.