PRB: Cannot Filter ADO Recordsets for Nulls (187871)

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

Note You must change User UID=<username> and PWD=<strong password> to the correct values before you run this code. Make sure that UID has the appropriate permissions to perform this operation on the database.

   '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=<username>;pwd=<strong password>;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")
				

      rs.filter= "state=null"