Microsoft KB Archive/237992

= PRB: Query Testing for NULL in Access Database Does not Return Records with Jet 4.0 =

Article ID: 237992

Article Last Modified on 12/5/2003

-

APPLIES TO


 * Microsoft Data Access Components 2.1 Service Pack 2
 * Microsoft Data Access Components 2.5
 * Microsoft Data Access Components 2.6
 * Microsoft Open Database Connectivity Driver for Access 4.0
 * Microsoft OLE DB Provider for Jet 4.0

-



This article was previously published under Q237992



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

SELECT * FROM Customers WHERE region = NULL SELECT * FROM Customers WHERE region = NOT NULL 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.



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

SELECT * FROM Customers WHERE region IS NULL SELECT * FROM Customers WHERE region IS NOT NULL



RESOLUTION
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.



STATUS
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
 Create an ODBC DSN in the ODBC Data Sources applet in the Control Panel that points to the Northwind.mdb file. 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.  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";  Create an edit box and bind it to the CustomerID field in the recordset to see the field on the form. Compile and run the program. No records will be returned.</li></ol>

MFC DAO
<ol> 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.</li> Apply the fix from the following article if using a non-unicode build.

235507 BUG: DAO 3.6 Causes Debug Errors in MFC DAO Non-Unicode Builds

</li>  Insert the following line in the application's InitInstance method to load DAO 3.6 as described in the following article.

237992 PRB: Unrecognized Database Format Error with Access 2000

AfxGetModuleState->m_dwVersion = 0x0601; This will ensure that the Jet 4.0 engine is loaded. </li>  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"; </li> Create an edit box and bind it to the CustomerID field in the recordset to see the field on the form.</li> Compile and run the program. No records will be returned.</li></ol>

ADO
<ol> Create a Win32 console application.</li>  Insert the following line to use ADO. </li>  Paste the following code into your main function: CoInitialize(NULL);
 * 1) import "C:\Program Files\Common Files\System\ado\msado15.dll" no_namespace rename( "EOF", "adoEOF" )

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; }

</li> Change the location of the Northwind.mdb file if necessary to reflect the location of the file on your system.</li> Compile and run the code. No records will be returned.</li></ol>

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

<div class="references_section">