Introduction
---------------
Many times folks encounter problems when dealing with Nulls. To begin the troubleshooting process, it is important to know what a Null is. Many folks, including myself, think of Null as "Undefined" or "Unknown". Null is not zero, nor is Null an empty string (an empty string is also known as a Zero Length String and is often referred to with the acronym ZLS). Along with a Null NOT being a Zero or a ZLS, a Null is also NOT equivalent to the special VBA values of Nothing (the value of an un-initialized object variable) or Missing (the value of an optional Variant parameter of a procedure that is not passed). Therefore, with all the things that a Null is NOT, we come to the conclusion, that a Null is "Undefined" or "Unknown".

What a Null is
-----------------
Null is a special value for fields in tables, as well as variables in VBA code. Although fields/columns of most data types can accept Null as a valid value, VBA variables with the data type of "Variant" are the only valid recipients of the value Null.

Difference between ZLS and Null
-----------------------------------------
To differentiate Nulls and ZLS's, lets consider names of people. Assume we have the fields FName, MName, LName in a table. The FName and LName are Required and have Allow Zero Length set to No, but MName is NOT required and ZLS's are allowed.

- If the MName is left as Null, then the middle name of the person is Unknown. Note that "Unknown" does not mean, "Does not exist".

- If the person DOES NOT HAVE a middle name, then the MName field could be set to a ZLS. Thus indicating the middle name IS known, and the person does NOT have one.

Law of Propagating Nulls (LOPN)
---------------------------------------
When working with Null values in expressions, whether in VBA, a control source property or an expression column of a query object, it is important to know how a Null value behaves within an expression as well as how a Null value affects the final result of an expression. The behavior of Null is especially good to know, when using the two valid VBA operators for text concatenation, the plus (+) and the ampersand (&).

In short, Null will propagate throughout an expression until you have an operator that prevents it. This behavior is known as the "Law of Propagating Nulls" (LOPN).

LOPN Behavior Examples
--------------------------------
Examples of the Law of Propagating Nulls are often the best explanation of this behavior.

Null > 1 returns Null
Null + 1 = Null
Null = Null returns Null. {This one seems difficult for folks … but replace the word 'Unknown' for Null… then you get … Unknown = Unknown .. and we don't know if the Unknowns are in fact equal, so the answer to the comparison is .. Unknown}

Null Or Null yields Null
Null Or True yields True
Null Eqv Null yields Null {read the note above with Null = Null}
Null And True yields Null

Null > "" = Null {the "" is a ZLS}
Null + "Hello" = Null
Null & "Hello" = "Hello"
(Null + "Hello") & (Null + "World") = Null
(Null + "Hello") & (Null & "World") = "World"
(Null & "Hello ") + (Null & "World") = "Hello World"

... there are, it seems, countless examples … so this is by no means an exhaustive list of possibilities! ...

Analogy
----------
Some folks have made the analogy that working with Null is like multiplying by Zero. We know that anything multiplied by 0 equals 0. In a similar vein, anything "mathematically" operated with a Null yields a Null. I use the term mathematically in quotes simply because in Access/VBA expressions, the addition (+) operator (typically thought of as a "mathematical" operator) is a dual purpose operator. It will add numbers, as well as "add" (or concatenate) text. Access/VBA also has another concatenation operator, the ampersand (&). But as seen above in the examples, when you "add" (+) text with a Null, the result is a Null, but when you concatenate (&) a Null with text, the result is that text. Therefore the ampersand is an operator that prevents Null propogation.

How can I use this info (practical examples)
----------------------------------------------------
Building a complete street address:
I often will use LOPN to build address strings in reports. I create ONE text box control (ie: txtAddress) on the report and set the Control Source property to something like this …


{Note: line breaks added for clarity}

Then the control could be set to a height of some sort and have the CanGrow = Yes and the CanShrink = Yes. With this type of set up the address on the report will NOT have any blank lines or extra commas.

Building a SQL statements WHERE clause from controls on a form:


Coercing Nulls without Nz()
Nz() is an Access function made available to VBA for Access. It is used to define a Null as a valid value, when Nulls are not allowed in the current situation. Like this:


In the code expression above, a Zero will be stored in the variable x, when DLookup() returns a Null.

However, since Nz() is an Access provided function, VBA for Excel and other VBA implementations do not have that convenience, so knowing alternatives for Null coercion helps make your User Defined Functions very portable!

In VBA … [*]If Len(Me.txtTextBox1 & "") > 0 Then … [*]If Me.txtTextBox1 & "" = "Completed" Then …

In Control Source expressions … [*]=IIF(Len([txtTextBox1]) & ""), "<N/A>", [txtTextBox1] * 15) [*]=Val(Me.txtTextBox1 & "")
Val() returns a 0 if me.txtTextBox1 is Null or a ZLS. Note that you can use Val() in VBA as well.

Sometimes NO NEED to Coerce Nulls
Remember a Null is NOT TRUE, nor is a Null FALSE, therefore a null will return the ELSE portion of an If..Then construct in VBA:



The "Do yet another thing" branch, in the above If..Then, will be taken if Me.txtTextBox1 is Null {Note: Len() returns a Null if a Null is passed to it}

Similarly behavior is seen in expressions for query columns and control sources

With the IIf() function, the "falsepart" parameter (which is essentially the "Else") is returned, if the evaluated expression yields a Null.

Notes about User Input:
------------------------------
When you enter "" (A ZLS)
- in a numeric field that IS NOT required, a Null will be stored.
- in a numeric field that IS required, you get an error.
- in a text field that allows ZLS's, a ZLS will be stored.
- in a text field that IS NOT required and DOES NOT allow ZLS's, a Null will be stored.
- in a text field that IS required and DOES NOT allow ZLS's, you get an error
- in an UNBOUND control, a Null is returned by the control

Wrap Up
------------
I hope these "thought starter" examples will help you out. Knowing how to deal with a Null value is the key to solving the errors associated with this highly useful special value.

UtterAccess Threads Discussing LOPN
----------------------------------------------
datAdrenaline: http://www.utteraccess.com/forums/showflat...all#Post1178396
strive4peace[current year]: http://www.utteraccess.com/forums/showflat...all#Post1549569
LPurvis: http://www.utteraccess.com/forums/showflat...all#Post1544046

Other Reference Articles regarding Nulls
-------------------------------------------------
http://allenbrowne.com/casu-11.html
http://allenbrowne.com/casu-12.html
http://allenbrowne.com/ser-64.html {this one is not directly related, but is a good read}
http://www.access-programmers.co.uk/forums...ad.php?t=131593 {this one is pretty deep … the kind of 'deep' that makes your brain hurt! … but if you're game for an analytical discussion on Nulls … have at it!}

-----------
Special thanks to the following folks for their feedback and commentary:
Bob Larson (boblarson)
Aquadevel (aquadevel)
Glenn Lloyd (argeedblu)
William Hammett (dashiellx2000)
Joan Wild (jwild)
Walter Niesz (niesz)


Edits:
12/10/07 - Added text per Brent's request. Doug
12/26/07 - Changed link reference per Brent's request. Glenn



Edited by: argeedblu on Wed Dec 26 8:38:11 EST 2007.