Learn the differences between Is Null and IsNull() in Access

Null values are an enigma to some and, as a result, are often handled badly by even the best IT pros. Few of us can totally escape the errors that pop up when null entries work their way into the application unexpectedly. Perhaps the best way to handle null entries is to avoid them altogether, but that isn’t always possible. Null values are valid in some cases, and when an application must accommodate them, you need to make sure Access finds them. The errors that result from improperly handled null entries aren’t always obvious and can show up later as erroneous data. By then, you may have trouble tracking the problem. In this article, I’ll show you how to use Is Null and IsNull().

Defining null
It’s easy to think of null and blank entries as the same thing, but they aren’t equal. The term “null” simply means the data is missing or unknown. That’s not the same as saying the data doesn’t exist, although it may not. Often, null entries are an indication that the value will be forthcoming or that someone is still searching for the data. Eventually, you may find that the data isn’t valid for that particular record, but until that consensus is reached, the value should remain null. For example, a customer may have several phone numbers, but your user may not know the fax number when he creates the new record. Until he obtains the number or learns that the customer has no fax at all, the fax entry is null. Eventually, he’ll either enter the correct number or indicate in some fashion that the data isn’t applicable to the record.

A little background
Is Null and IsNull() both find null values, but you won’t use them in the same way. You would use Is Null and Is Not Null in query expressions and SQL WHERE clauses. IsNull(), on the other hand, is a Visual Basic for Applications (VBA) function and would be used only in VBA modules. Null is a reserved word and represents a null entry in expressions, but you will seldom use this value alone. You certainly don’t need to enter it as a value. As long as you enter nothing at all, Access assumes a Null value for the entry. In this article, I am using lower case when referring to the general null condition; proper case denotes an actual Null value.

Using Is Null and Is Not Null
You should combine the SQL Is operator with Null to find Null values. You can’t use the = operator to compare Null values to anything, as the result will always be Null. This isn’t always true outside of Access, so be careful when passing criteria expressions containing Null via ODBC and OLE DB connections. Add SQL’s Not operator to find values that aren’t Null.

Using Northwind, the sample database that comes with Access, I will create a few queries to illustrate using Is Null and Is Not Null. First, to find Null values in the Region field in the Employees table, base a new query on that table and add the LastName and Region fields to the grid. In the Region field’s Criteria cell, enter Null or Is Null to complete the query shown in Figure A. (If you enter Null, Access changes it to Is Null for you.) The results, shown in Figure B, identify the Null Region values by employee.

Figure A

Use Is Null to find Null values.

Figure B

There are four records with Null Region values.

Now, add Not to the mix to find records where the Region field isn’t Null. To do so, return to the query to Design View and add the Not operator, as shown in Figure C. Run the query to see the results shown in Figure D.

Figure C

Is Not Null will exclude Null values from the results.

Figure D

This time, the query returns Region values that aren’t Null.

A WHERE clause example
You’ll also use the Is Null form in SQL WHERE clauses. A good example is the OpenForm method as it filters a form’s recordset. To illustrate using Is Null in this situation, launch the Visual Basic Editor (VBE) and enter the following in the Immediate window:
DoCmd.OpenForm “Employees”,,,”Region Is Null”

Then, return to Access and you’ll find the Employees form open. The navigation bar denotes a filtered set of four records. Click the Personal Info tab and check the Region control—it’s empty (Null). If you browse all the records in this filtered set, you’ll find that each Region value is Null. To exclude Null Region values, simply add the Not operator as follows:
DoCmd.OpenForm “Employees”,,,”Region Is Not Null”

Using IsNull()
The IsNull() function belongs to the VBA library and returns a Boolean value that indicates whether an expression or variable is Null. To illustrate, let’s build a simple form and use IsNull() to check the value in a text box control. To do so:

1. 1.      Open a blank form and add two text box controls to the form. Name the controls txtOne and txtTwo.
2. 2.      Click the Code button on the Form Design toolbar to launch the VBE.
3. 3.      Enter the event procedure shown in Listing A.
4. 4.      Open the form in Form view and tab past txtOne, enter a value in just txtTwo, and then press Tab to execute txtTwo’s LostFocus event.

The txtTwo control’s LostFocus event tries to display the results of the expression txtOne & txtTwo, but it can’t because txtOne is Null. Consequently, the expression raises the error shown in Figure E. Clear the error and return to the form’s module. Replace the LostFocus event procedure with the one in Listing B.

Figure E

txtOne’s null value returns an error.

Return the form to Form View and leave at least one of the controls empty and tab past txtTwo to trigger its LostFocus event. The procedure will display the simple message shown in Figure F, instead of raising the previous error. When both controls contain a value, the Else condition displays that result of the txtOne & txtTwo expression. (There are simpler ways to determine a control’s value—this example simply illustrates using VBA’s IsNull() function.)

Figure F

VBA’s IsNull() function also finds Null values.

To exclude Null values, simply add the SQL operator in the form:
Not IsNull(field)

Such a statement will match entries that aren’t equal to Null. Use the IsNull() function in your VBA code to find Null values before they have a chance to return errors.

The key is to know Null values exist
If an application must accommodate Null values, handle them correctly. The key to handling them correctly is to know they are there. Use Is Null and Is Not Null in criteria expressions and IsNull() in VBA code to find Null values before they work their way into your application with unexpected and often negative consequences.