Null Values

A Null value is a value that is unavailable, unassigned, unknown or inapplicable
A Null Value is like a character string of length zero. You cannot load a null value into a numeric column. If a column has a "Not Null" constraint it means the field is mandatory and cannot be null
Never use null to represent a value of zero
If a row lacks the data value for a particular column, that value is said to be null or to contain a null
Columns of any data type can contain a null value unless the column was defined as NOT NULL or as a primary key when the column was created
If any column value in an arithmetic expression is null then the result is null
A Null value takes up one byte of storage and indicates that a value is not present as opposed to a space or zero value.

--This does not work in SQL Server

SELECT     Address 
FROM       EMPLOYEES 
--WHERE    ISNULL(Address) = False   -- this is for Access 
WHERE      Address IS NOT NULL       -- this is for SQL Server 

IS NULL

A null value is not an "Empty Field" or a "No Value At All"
A null value indicates that the data is missing or unknown
An unhandled null value is a sign of a lazy or inexperienced developer

Can you have multiple rows containing a null value ??

Where errors are concerned, null values are an equal-opportunity menace. If an unhandled null value doesn't generate a runtime error, it'll show up in erroneous data. Neither problem is your run of the mill "oops, there's a bug" error. In fact, an unhandled null value is the sign of a lazy or inexperienced developer. When null values are acceptable values, and they often are, you must handle them upfront and aggressively.

Knowing null

You can't handle a value properly if you don't understand its nature. A common misconception is that a null value is simply an empty field or no value at all. That's not true. A null value indicates that the data is missing or unknown. Occasionally, a null value does mean that the data doesn't exist or isn't valid for that particular record, but the concepts aren't interchangeable.

IS NULL

This operator is used to determine if a field doesn't contain data

SELECT     * 
FROM       Employees 
WHERE      ReportsTo IS NULL 

NOT IS NULL

This operator is used to determine of a field does contain data.

SELECT     * 
FROM       Employees 
WHERE      ReportsTo IS NOT NULL 

Dealing with null

Since Access allows null values, it's your job to determine whether you want to store them. Generally, the data will be your best guide. If the nature of the data requires that all data be present to save the record, you can handle null values at the table level. Simply set the field's Required property to Yes and bypass the problem. Be prepared for the rules to change.

Few applications are so tight that nulls aren't present. If users need the flexibility to create records without entering all of the data at the time they create the record, you have a choice. Allow the table to store a null value or use a default expression that stores an appropriate text message, such as "NA" or "Pending."

Unfortunately, this solution works only for text fields. For numeric fields, you could use a default value of 0, but that might cause trouble in the long run because functions handle Null and 0 differently (see #7). In addition, the Default property works only for new records. That means that you can't apply this solution to existing records. The truth is, it's usually easier to handle null values than it is to usurp them in this fashion.

Not equating null

Don't try to find null values by equating them to anything else. The following expressions return an error, regardless of anything's value:
anything = Null anything <> Null
As far as Access is concerned, Null doesn't equal anything. You can't use the Equals operator (=) to find null values. Nor can you use the Inequality operator (<>) to exclude them. (This isn't always true outside Access.)

Finding or excluding null values

Once you decide that null values are acceptable, it's your job to accommodate them throughout the application.
To find or exclude null values, use Is Null and Not Is Null, respectively, in criteria expressions and SQL WHERE clauses.
For instance, to find null values in a query, you'd enter Is Null in the appropriate field's Criteria cell.
When building a WHERE clause, use Is Null and Not Is Null as follows:

WHERE      source.field Is Null 
WHERE      NOT(source.field) Is Null 

Protect VBA expressions from errors by using IsNull()and Not IsNull().For instance, the use of IsNull() in the following If statement handles a potential runtime error when null values exist:
If Not IsNull(field) Then ...
Although Is Null and IsNull() have similar functions, they're not interchangeable.

Working around null

Access won't always work with null values as you might expect. If you allow them, be prepared for surprises. For instance, a simple expression such as
GrandTotal = Subtotal + Shipping
becomes a problem if Shipping contains null values. Instead of returning just the Subtotal, as you might expect, the expression returns Null. That's because any equation that encounters a null value will always return Null. Although it's a nuisance, it makes sense. You can't evaluate an unknown value.

If your data contains null values, use the Nz() function to protect your expressions from this error. Specifically, Nz() returns a value other than Null when it encounters Null as follows:
GrandTotal = Subtotal + Nz(Shipping)
In this case, Nz() returns 0 when Shipping equals Null. Use Nz() in criteria and VBA expressions. Access projects don't support Nz(). Instead, use Transact SQL's IsNull function.

Finding null values using ADO

Null doesn't equal anything. That's true, as long as you're using native functions and VBA. It isn't true if you're manipulating data via the ActiveX Data Object (ADO) library. For instance, the following statement executed against an ADO Recordset object returns an error:
rst.Find "FaxNumber Is Null"
That's because ADO doesn't recognize the Is operator in this context. The ADO library supports the Equals and Inequality operators when searching for or excluding null values. Fortunately, the correction is as simple as replacing the Is operator with the Equals operator:
rst.Find "FaxNumber = Null"
To exclude null values using ADO, use the Inequality operator:
rst.Find "FaxNumber <> Null"
You'll find Access a bit of an oddball on this issue. Many libraries use the Equals and Inequality operators instead of Is. If a non-native library returns an error when working with null values, this switch will probably do the trick.

Understanding the inconsistency of SQL aggregates

Not all aggregate functions consider null values. The good news is, there's a bit of reason to the inconsistency. An aggregate function that evaluates a field does not evaluate null values in its result. However, Count(), First(), and Last() do evaluate null values. It makes sense that they would--just because one field contains a null value doesn't negate the row's purpose within the context of the domain. For instance, Count(*) counts the total number of rows in a recordset even if some of those rows contain null values. If you want to exclude null values in a count, specify the field in the form Count(field). The result of both forms may or may not be the same. The point is, the field-specific form won't consider null values in its count.

Including null values in a conditional search

When using a WHERE clause to find or restrict data, you must explicitly specify null values.
Otherwise, Jet excludes the row from the results.
This behavior is inherent in the equality issue discussed in #3.
Because Null doesn't equal anything, it can't satisfy a condition other than Is Null.
For instance, the simple expression

WHERE      field < 5 

will return all the records where field is less than 5--except for those records where field is Null.
Now, that might be what you want, but it might not.
If you want to include null values, include Is Null in the condition as follows:

WHERE      field < 5 OR field Is Null 

Excluding null values in a group

Jet SQL's GROUP BY clause doesn't eliminate null values from the grouped results.
Instead, Jet sorts null values to the top or the bottom of the result set, depending on the sort order.
For instance, the following query includes records where the Region field is Null:

SELECT     FirstName, LastName, Region 
FROM       Employees 
GROUP BY   Region 

The result isn't right or wrong, it just might not be what you want.
You must explicitly exclude null values. In this case, you'd add a HAVING clause as follows:

SELECT     FirstName, LastName, Region 
FROM       Employees 

GROUP BY Region
HAVING Not (Region) Is Null
There's no specific method for explicitly excluding null values. The statement's purpose will dictate the solution.

Using null to spot normalization problems

A null value is an acceptable value. However, too many null values often point to an unnormalized table.
For instance, if you store customer phone and fax numbers, you might end up with a lot of empty fax number fields.
(Even if you have no null values, your table's still not normalized properly, in this case.)

To normalize the phone data, you'd add a table that includes three fields: the foreign key column that relates the phone record to its corresponding customer, the phone number type, and the phone number.
The phone number type would identify the phone number as an office, fax, home, cell, and so on.
Then, you'd enter phone number records only when appropriate, eliminating null values.
If the customer has no fax, there'd be no record for a fax number.