ADO filter with NULL value

NULL value in database means no storage in the cell, if you use ADO to query the data and you would like to filter the NULL value, it will be a challenge for a lot of programmers.

Let's see the following code:-

dim rs, strSQL
strSQL = "SELECT EmployeeId, ReportsTo FROM Employees"
Set rs = CreateObject("ADODB.Recordset")
rs.cursorlocation = 3 ' -- adUseClient
rs.Open strSQL, connobj ' -- Assume the connobj already has open connection
do while not rs.eof
rs.movenext
loop
rs.close

The code above is retreiving data from MS SQL Server Northwind database, it tries to get the employeeId and superior employee Id (reportsTo column).

Let's say the requirement is those employee without superior display in one panel and those employee with superior display in another panel.

I believe no one want to query the database two times by using SQL query "SELECT EmployeeId, ReportsTo From Employees WHERE ReportsTo IS NULL" and "SELECT EmployeeId, ReportsTo From Employees WHERE ReportsTo IS NOT NULL", so we will query one time from the database by re-using the above code.

You will add another code before the while loop:-
rs.filter = "ReportsTo IS NULL"

Immediately you will get this error:-
Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another

The reason being is because ADO Filter does not support "IS" and "NULL" statement, even you tried to use the filter as ISNULL(ReportsTo, 'N/A') = 'N/A' also get the same problem.

The only solution to this is back to the SQL query, change the query as
SELECT EmployeeId, ISNULL(ReportsTo, 'N/A') FROM Employees

Then the rs.filter use ReportsTo = 'N/A' will solve the problem, the whole code is shown below:-

dim rs, strSQL
strSQL = "SELECT EmployeeId, ISNULL(ReportsTo, 'N/A') FROM Employees"
Set rs = CreateObject("ADODB.Recordset")
rs.cursorlocation = 3 ' -- adUseClient
rs.Open strSQL, connobj ' -- Assume the connobj already has open connection
rs.Filter = "ReportsTo = 'N/A'"
do while not rs.eof
' -- Display Data in Superior Panel
rs.movenext
loop
rs.Filter = "ReportsTo <> 'N/A'"
do while not rs.eof
' -- Display Data in Subordinate Panel
rs.movenext
loop
rs.close



Cheers!