PRB: Cannot Filter ADO Recordsets for Nulls

ID: Q187871

The information in this article applies to:

SYMPTOMS

Using the Filter property of an ActiveX Data Objects (ADO) Recordset to filter out records containing NULLs always fails. It may fail with the following error or may accept the Filter property, but fail to apply the filter:

Run-time error '3001': The application is using arguments that are of the wrong type, are out of acceptable range, or are in conflict with one another.


CAUSE

Currently, it is not possible to use the ADO Recordset's Filter property to filter records for NULL values.


RESOLUTION

Change the SQL statement used to populate the recordset so that it handles the nulls. For instance, the following would only retrieve the records that have a null value for state value:

rs.Open "select * from authors where state is null", cn


Another workaround is to first loop through the recordset and build an array of bookmarks for the records where the desired column is null (or not null) and then filter on the bookmark array. This method does require the recordset to support bookmarks.

Sample Code


   'Don't forget to add a reference to the
   'Microsoft ActiveX Data Objects Library
   Dim cn As New Connection
   Dim rs As New Recordset
   Dim vFields() As Variant

   'This example is connecting to SQL Server's Sample Pubs database.
   'You may want to change the server name to reference your local
   'SQL Server
   cn.Open "driver={SQL Server};" & _

      "server=local;uid=sa;pwd=;database=pubs"

   rs.CursorLocation = adUseClient
   'Create some records with Null values
   cn.Execute ("update authors set state = NULL where state = 'UT'")

   rs.Open "select * from authors", cn, adOpenStatic, adLockBatchOptimistic

   'Build an array of bookmarks of records where desired field contains
   'null.
   i = 0
   Do While rs.EOF <> True

      If IsNull(rs(6).Value) Then
         ReDim Preserve vFields(i)
         vFields(i) = rs.Bookmark
         i = i + 1
      End If
      rs.MoveNext

   Loop

   Debug.Print rs.RecordCount
   rs.Filter = vFields
   Debug.Print rs.RecordCount

   'Reverse the changes made by the .Execute call above.
   cn.Execute ("update authors set state = 'UT' where state = NULL")


STATUS

This behavior is by design.


REFERENCES

Data Access SDK Help; search on: "Filter Method"

ADO Help; search on: "Filter Method"

Additional query words: kbnokeyword kbado200 kbDatabase kbSweepNext kbVBp600 


Keywords          : kbDatabase kbVBp600 kbSweepNext 
Version           : WINDOWS:2.0
Platform          : WINDOWS 
Issue type        : kbprb

Last Reviewed: May 27, 1999