Null Values and How Access/Jet deals with them.

What Is "Null"?
A field in a table, or control on a form or report which does not have a specific value entered in it is said to have a Null value. This is different from a default value as 0 on a numeric field or an empty string in a text field or string variable.

The meaning of a null value within a table depends upon the context of the table, but most of the time it is an optional field that hasn't had anything entered into it for that particular record.

Null values in variables or controls on a form/report will cause some functions to fall over with a runtime error, because they are expecting a value to work on. For example, if txtCustomerID is Null, this following code will cause the debugger to open and the cursor to highlight this line of code:
In code, you cannot check for a value being equal to Null, as you can any other value, you need to check whether a field Is Null.
The method of picking it up is the same regardless of the field's data type in the underlying table.

With SQL code it is done thus:
or using VBA with the IsNull function. Thus:
These conditions can be inverted, so you can check for fields where they are not null thus:
and:

Null Values in variables and form/report fields
The only type of variable that can hold a Null value is a Variant, as strings are initialised to an empty string ("") and numeric ones (including Yes/No) are initialised to 0 (No/False is stored as 0; True/Yes is stored as -1).

Within VBA code, null values can cause problems when manipulating the contents of text fields and textboxes, so precautions have to be taken.

A nifty way of getting around this problem in text boxes and fields for holding text values is to append an empty string to it and check that the length of this is greater than 0. Many of my VBA code postings use this construct:
This works because null appended to an empty string is an empty string, and an empty string has a length of 0, so if there is a value in the textbox, it won't affect the output.

Null values in numeric fields
Null values in database fields and variables that store numeric values are ignored when using Group By queries and aggregate SQL functions such as Sum, Avg, Count and Var, as well as the equivalent domain functions DSum, DAvg, DCount and DVar.

This can cause unexpected results in the output. For example:

With a data set of 10, 20, null, 40

Avg and DAvg of the data is 23.333 recurring, ie Access ignores the Null value, so the calculation is (10+20+40)/3

Count/DCount returns 3 (ditto)
Sum/DSum of the data however is 70 as you would expect.

Performing calculations with null values is another area which can cause problems for the unwary. If there is the possibility of one value being Null, the Nz function can be used to substitute a value to use in case it is to stop the calculation returning Null. Thus:

intResult = 100 * Nz(Me!txtTextbox, 1)

This function call will return 1 if the value of txtTextbox on the current form is null.

A reasonable analogy in terms of calculations is that if you imagine Null as the same as infinity you won't get too far wrong, in that you will always get null as the output.

Avoiding Nulls
The easiest way of avoiding Null values in the first place is to ensure that the database fields are set to require entry in those fields where it is required, and that code in forms is set to display an appropriate error message for those fields where entry is required, if the user tries to bypass entering information.

Summary
Null values are something that database professionals (designers, developers and DBA's) will deal with on a daily basis and ensuring that their code can cope with them is an essential part of their knowledge. By using the information here it shouldn't be too difficult to write robust applications that don't have problems dealing with them.