PRB: Cannot Filter ADO Recordsets for Nulls

ID: Q187871

The information in this article applies to:

ActiveX Data Objects (ADO), version 2.0
Microsoft Data Access SDK, versions 2.0, 2.1

SYMPTOMS

Using the Filter property of an ActiveX Data Objects (ADO) 2.0 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

Itt is not possible to use the ADO Recordset's Filter property to filter records for NULL values using ADO 2.0.

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 field:

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:

Keywords : kbADO200bug kbDatabase kbVBp600 kbGrpVBDB kbGrpMDAC kbDSupport kbMDAC210SP2
Version : WINDOWS:2.0,2.1
Platform : WINDOWS
Issue type : kbprb