PRB: Can't Find NULL Records with Parameterized QueryLast reviewed: May 8, 1997Article ID: Q168207 |
The information in this article applies to:
SYMPTOMSRunning a parameterized query against a Jet database where the parameter variable is equal to NULL will not return any records. Running a query without the parameter variable that specifically searches for NULL will succeed as expected.
CAUSEA variable with a NULL value sent in as the parameter is not equal to what the JET engine recognizes as NULL. The values of the NULLs do not match.
RESOLUTIONBefore calling the code that executes the query, use an IF statement that checks the value of the parameter. If the value is NULL, run a different query that specifically looks for a NULL value, otherwise run the original, parameterized query. Example:
Dim p1 as long dim r1 as recordset dim SQLstring as string dim p1 as long if p1 = NULL then SQLstring = "SELECT Table1.Field1 FROM Table1 _ WHERE (((Table1.Field1) Is Null));" 'Looking specifically for a Null else SQLstring = "SELECT Table1.Field1 FROM Table1 _ WHERE (((Table1.Field1)=[" & p1 & "]));" 'Parameterized query end if set r1 = MyDatabase.openrecordset(SQLstring, dbOpenDynaset) STATUSThis behavior is by design.
MORE INFORMATIONSteps to Reproduce Behavior
Query1 will have one record while Query2 will have no records. |
Keywords : APrgDataAcc APrgDataOther vb5all kbprb
© 1998 Microsoft Corporation. All rights reserved. Terms of Use. |