Visual Basic for Applications (VBA) consistently supports a set of rules for using SQL WHERE criteria to search for data. If you're like most developers, you expect that set of rules to be supported in any library that Access supports. Unfortunately, you'd be wrong—once you leave the world of Access and VBA, the library rules take precedent, not VBA. For instance, the ActiveX Data Objects (ADO) library doesn’t use the same rules for searching for Null values as VBA and Access. If you try to apply VBA rules to ADO, your code may return an error. Being forewarned is being forearmed so they say, and in this article, I'll warn you about an inconsistency between the VBA and ADO libraries when searching for Null values.
Finding Null values in SQL WHERE
There are a number of reasons to search records—you may want to limit the records you submit to an action query or limit the records populating a form or report. I won't try to review every filtering possibility, but many VBA statements accept a filtering argument equal to a WHERE clause without the WHERE keyword.
You might find the following guidelines helpful when searching for Null values in Access and VBA:
- You can't use the Equals operator (=). As far as Access is concerned, nothing actually equals a Null value. This isn't always true outside of Access—I'll discuss this in a bit.
- Use Is Null and Is Not Null in query expressions and SQL WHERE clauses.
- Use IsNull() or Not IsNull() in VBA functions.
Let's look at a simple OpenForm example in Listing A that offers a WHERE argument.
The wherecondition argument is equal to a SQL WHERE clause without the WHERE keyword. For example, the following statement populates a form with only those records where the Partner value equals the value stored in the variable PartnerID:
DoCmd.OpenForm "frmContracts", acNormal, , "Partner = " & PartnerID
(In the above statement, you can tell that PartnerID is a number data type because there are no delimiters in the clause.)
But suppose you're searching for Null values, or excluding them. In such a case, you might try one of the following statements:
DoCmd.OpenForm "frmContracts", acNormal, , "Partner Is Null"
DoCmd.OpenForm "frmContracts", acNormal, , "Partner Is Not Null"
The first statement will populate the form with all the records where the Partner field is Null; the second statement does just the opposite, retrieving all the records where the Partner isn't a Null value.
The ADO problem
The first guideline mentioned above creates a conflict when using SQL WHERE to find records using an ADO object, such as a Recordset. To illustrate this conflict, run the procedure in Listing B in Northwind, the sample database that comes with Access.
Open a blank module, enter the procedure and then execute the following statement in the Immediate window:
Doing so returns the error shown in Figure A. The evaluated results of strCriteria look correct, so what's the problem?
|ADO returns an error when you use the Is operator with the Null value.|
Clicking Debug in the error dialog box clues us in just a bit because VBA then highlights the Find method right before the Do loop, as shown in Figure B.
|Click Debug to find the erring statement.|
The Find method doesn't like the evaluated WHERE clause, but that in itself isn't very helpful. You need to know why it doesn't like the clause since the clause appears to be correct by Access and VBA standards.
Remember when I mentioned that the Equals operator rule isn't always applied outside of Access? That's the root of the ADO error—ADO supports the Equals operator. When working with an ADO method you must use the Equals and Is Not Equal To operators instead of the IS operator as follows:
field = Null
field <> Null
Fortunately, you need change only one line in your earlier procedure (Listing A). Substitute the strCriteria definition statement:
strCriteria = fld & " Is Null"
With the appropriate Null syntax:
strCriteria = fld & " = Null"
In truth, all you need to do is replace the IS operator with the = operator. After doing so, run the procedure from the Immediate window again. (The results are shown in Figure C.) The evaluated strCriteria value is Region = Null and the following employee records have a Null value in the corresponding Region field: Buchanan, Suyama, King, and Dodsworth.
|Replace the Is operator with the = operator when using ADO.|
At this point, you might be wondering how to exclude Null values. Access and VBA use the Not operator, but the following statement will return an error in ADO; use the Is Not Equal To operator (<>):
strCriteria = fld & " Not Null"
To exclude Null values, replace the = operator in the current statement with the <> operator and run the function one last time. This time, the strCriteria variable evaluates to the following:
Region <> Null
As a result, the ADO Find method stops at the records for Davolio, Fuller, Leverling, Peacock, and Callahan, as shown in Figure D.
|Use the Is Not Equal To operator when excluding Null values from an ADO search.|
All things being equal
Access and VBA are fairly consistent in behavior and rules in the way they find (or exclude) Null values. You can't expect the rest of the world to behave the same way though. Once you step outside of VBA, you may be faced with a completely different set of rules, so be prepared to adapt. VBA uses the Is and Is Not operators while ADO uses the = and <> operators to search for Null values. Knowing the difference can save you a lot of troubleshooting time and frustration.
Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.