What is Null?

[ADezii]

Null as it relates to database development is one of life's little mysteries and a topic of total confusion for novices who venture out into the database world. A Null Value is not zero (0), a zero (0) length string, an empty Field, or no value at all - so exactly what is Null? The purpose of this Topic is hopefully to explain what a Null Value is, discuss some peculiarities about Nulls, show how we can detect them, and finally, how to convert them into not-so-devious entities.

1. What is a Null Value?
   1. Null is a Variant subtype used to indicate that a data item contains no valid data, is missing data, or the data is simple unknown. This special value is not a real value like "Smith", #12/24/2006#, 25.98, etc. but is an indicator that data is missing, unknown, or not applicable. The data in a Table or Query Field, as well as in a Form or Report Control has the Variant Data Type by default. When you leave a Field or Control blank, Null is stored automatically. One critical point to remember, and one worth restating, is that only the Variant Data Type con contain Nulls.
2. Some peculiarities concerning Null Values.
   1. If 'any' part of an Expressions evaluates to the Null Value, the entire Expression also has the Null Value. This is called propagation of Null Values and is a critical reason why we should take steps to handle them.
   2. If the Argument of a built-in or custom Function evaluates to Null, the Function usually returns a Null Value. For example, if you use SQL or an Aggregate Function to calculate a summary value of a Field for a group of Records, Records with Null Values in the Field won't be counted.
   3. When you join Tables in a Query, Records with Null Values in the join Field are not included in the Query result.
   4. When you create a Relationship and enforce Referential Integrity, you can still create Orphans in the Child Table by leaving the join Field blank in the Child Table.
3. How can you detect Null Values?
   1. Fortunately, we have a Function that returns a Boolean value indicating whether or not an expression contains valid data (Null). This Function is the IsNull() Function, and consists of only 1 required Argument:

   IsNull(<expression>)      'returns True if expression is Null
   'The required expression argument is a Variant containing a numeric expression or string expression.
    

4. Converting Nulls to something less harmful.
   1. Fortunately, we have an ally in the never ending fight against Nulls, and this is the Nz() Function. You can use this Function to return zero, a zero-length string (" "), or another specified value when a Variant is Null. For example, you can use this function to convert a Null value to another value and prevent it from propagating through an expression.
      
      

      Nz(variant, [valueifnull])

      The variant Argument is a variable of data type Variant. The valueifnull Argument is Optional (unless used in a query). It is a Variant that supplies a value to be returned if the variant argument is Null. This argument enables you to return a value other than zero or a zero-length string.
      
      If the value of the variant argument is Null, the Nz function returns the number zero or a zero-length string (always returns a zero-length string when used in a query expression), depending on whether the context indicates the value should be a number or a string. If the optional valueifnull argument is included, then the Nz function will return the value specified by that argument if the variant argument is Null. When used in a query expression, the NZ function should always include the valueifnull argument,
      
      If the value of variant isn't Null, then the Nz function returns the value of variant.

   Dim varVariant As Variant
   varVariant = Null
    
   Debug.Print Nz(varVariant)              'returns a Zero length String
   Debug.Print Nz(varVariant, "Null")      'returns the String Null
    
   Dim intTest As Integer
   intTest = 600
   Debug.Print Nz((intTest * varVariant) / 16, 9999)      'returns 9999

[nico5038]

Although the Null propagation can cause havoc, it's also an opportunity to save on coding.

The regular occurring problem of having a person's name "in parts" and wanting it to concatenate into a readable string often leaves us with an additional space like:

Select [Lastname] & ", " & [MiddleInitial] & " " & [firstname] as Fullname, ...
 

will have two spaces when there's no [MiddleInitial].
Often this is solved with:

Select [Lastname] & ", " & IF(IsNull([MiddleInitial]),"",[MiddleInitial] & " ") & [firstname] as Fullname, ...
 

But using the Null propagation and the plus ("+") sign we can use:

Select [Lastname] & ", " & [MiddleInitial] + " " & [firstname] as Fullname, ...
 

Here the "+" will cause Null propagation and thus the trailing space won't show.

Nic;o)

[ADezii]

Although the Null propagation can cause havoc, it's also an opportunity to save on coding.

The regular occurring problem of having a person's name "in parts" and wanting it to concatenate into a readable string often leaves us with an additional space like:

Select [Lastname] & ", " & [MiddleInitial] & " " & [firstname] as Fullname, ...
 

will have two spaces when there's no [MiddleInitial].
Often this is solved with:

Select [Lastname] & ", " & IF(IsNull([MiddleInitial]),"",[MiddleInitial] & " ") & [firstname] as Fullname, ...
 

But using the Null propagation and the plus ("+") sign we can use:

Select [Lastname] & ", " & [MiddleInitial] + " " & [firstname] as Fullname, ...
 

Here the "+" will cause Null propagation and thus the trailing space won't show.

Nic;o)

Excellant point Nic;o, unfortunately this may be the only case where Null Propagation would be an Asset rather than a Liability. Thanks for the Tip, it's definitely work keeping in mind.

[NeoPa]

ADezii:
Excellent point Nico, unfortunately this may be the only case where Null Propagation would be an Asset rather than a Liability.

I would have to disagree there my friend.

The Null is a tremendously useful animal to have available. There are many situations where it is important to differentiate between a value (of whatever type) and no value at all (or Null). When usage of the Null is properly understood it can be very beneficial. Clearly it's confusing to novices and that's perfectly understandable, but it's not useless merely because it's hard to understand.

Many things can be handled more easily by simply choosing a specific value to convert it to when it does occur certainly, but that's not the whole story. Indeed, I use that technique myself quite heavily, but it's not for all situations.