When using an SQL statement with a WHERE clause that contains a test for NULL using the = operator, records that match the query are not returned in the recordset. This happens with Jet 4.0 when using DAO, ODBC or OLE DB. Here are a few examples of such queries:
These queries worked correctly with Jet 3.5 and returned the records as expected.
This behavior will only be exhibited on computers with MDAC 2.1 and later that have Jet 4.0 installed.

Jet 4.0 made some changes to the SQL syntax and is now more SQL ANSI 92 compliant than before. Especially SQL ANSI 92 NULL behavior was implemented. Also remember that the result of a Boolean expression can have three results - TRUE, FALSE and NULL (in some documents referred as UNKNOWN).

The ANSI compliant syntax for testing NULL values uses the IS keyword. Examples of queries that work are:

Use the IS keyword in queries that test for NULL. This change will not break on systems with Jet 3.5 as Jet 3.5 accepted both types of queries.

This behavior is by design.

Steps to reproduce

All three sections use the Northwind sample database that can be installed with Access 97. If installed, it will be located in the Office\Samples subdirectory of your Microsoft Office installation.

MFC ODBC

1. Create an ODBC DSN in the ODBC Data Sources applet in the Control Panel that points to the Northwind.mdb file.
2. Create an MFC AppWizard application with database support. Use ODBC and specify the Customers table from the ODBC source created in the previous step as the source for your recordset.
3. Set a filter on the recordset before it is open. In your view class, insert the following line as the second line in the OnInitialUpdate() method.

   m_pSet->m_strFilter = "Region = NULL";
   						

4. Create an edit box and bind it to the CustomerID field in the recordset to see the field on the form.
5. Compile and run the program. No records will be returned.

MFC DAO

1. Create an MFC AppWizard application with database support. Use DAO and specify the Customers table from the Northwind sample database as the source for your recordset.
2. Apply the fix from the following article if using a non-unicode build.
   235507  (http://kbalertz.com/Feedback.aspx?kbNumber=235507/EN-US/ ) BUG: DAO 3.6 Causes Debug Errors in MFC DAO Non-Unicode Builds
3. Insert the following line in the application's InitInstance method to load DAO 3.6 as described in the following article.
   237992  (http://kbalertz.com/Feedback.aspx?kbNumber=237992/EN-US/ ) PRB: Unrecognized Database Format Error with Access 2000

   AfxGetModuleState()->m_dwVersion = 0x0601;
   						

   This will ensure that the Jet 4.0 engine is loaded.
4. Set a filter on the recordset before it is open. In your view class, insert the following line as the second line in the OnInitialUpdate() method.

   m_pSet->m_strFilter = "Region = NULL";
   						

5. Create an edit box and bind it to the CustomerID field in the recordset to see the field on the form.
6. Compile and run the program. No records will be returned.

ADO

1. Create a Win32 console application.
2. Insert the following line to use ADO.

   #import "C:\Program Files\Common Files\System\ado\msado15.dll" no_namespace rename( "EOF", "adoEOF" )
   						

3. Paste the following code into your main function:

   CoInitialize(NULL);
   
   try
   {
   	_RecordsetPtr pRs;
   	pRs.CreateInstance(__uuidof(Recordset));
   	pRs->Open(L"SELECT * FROM Customers WHERE Region = NULL",
   				L"Provider=Microsoft.Jet.OLEDB.4.0;"
   				L"Data Source=C:\\Program Files\\Microsoft Office\\Office\\Samples\\Northwind.mdb;"
   				L"Persist Security Info=False",
   				adOpenStatic,
   				adLockOptimistic,
   				-1)<BR/>
   
           if (pRs->adoEOF)
   	        cout << "No records returned.\n";
   
   	while (!pRs->adoEOF)
   	{
   		cout << (char*) (_bstr_t) pRs->GetFields()->GetItem(L"CustomerID")->GetValue() << endl;
   		pRs->MoveNext();
   	}
   
   
   }
   catch (_com_error& e)
   {
   	cout << (char*) e.Description() << endl;
   }
   
   
   						

4. Change the location of the Northwind.mdb file if necessary to reflect the location of the file on your system.
5. Compile and run the code. No records will be returned.

Resolution

Change the SQL string in all three sections from = NULL to IS NULL and the recordsets will return the records correctly.

For additional information, click the article numbers below to view the articles in the Microsoft Knowledge Base: