PRB: Cannot Find NULL Records with Parameterized Query (168207)
The information in this article applies to:
- Microsoft Visual Basic Professional Edition for Windows 5.0
- Microsoft Visual Basic Professional Edition for Windows 6.0
- Microsoft Visual Basic Enterprise Edition for Windows 5.0
- Microsoft Visual Basic Enterprise Edition for Windows 6.0
This article was previously published under Q168207 SYMPTOMS
Running 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.
CAUSE
A 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.
RESOLUTION
Before 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)
STATUS
This behavior is by design.
Modification Type: | Minor | Last Reviewed: | 1/8/2003 |
---|
Keywords: | kbprb KB168207 |
---|
|