UtterAccess HomeUtterAccess Wiki

Welcome Guest ( Log In | Register )

Custom Search
Edit Discussion
> Nulls And Their Behavior    
Nulls And Their Behavior

Many times folks encounter problems when dealing with Nulls. To begin the troubleshooting process, it is important to know what a Null is. Many people, 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".

Contents

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.

Practical Examples

Building a complete street address:

LOPN can be used to build address strings in reports. Create ONE text box control (ie: txtAddress) on the report and set the Control Source property to something like this …

CODE

=[LastName] & (", " + [FirstName])
   & (Chr(13) + Chr(10) + [AttentionTo])
   & (Chr(13) + Chr(10)+ [Street])
   & (Chr(13) & Chr(10) & [City] & (", " + [State]) & "  " & [zip])

{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:

CODE

Dim strSQL As String

strSQL = "SELECT * FROM tblMyTable WHERE (1=1)"
strSQL = strSQL & (" And TextField1 = '" + Forms("frmSomeForm")!txtTextBox1 + "'")
strSQL = strSQL & (" And TextField2 = '" + Forms("frmSomeForm")!txtTextBox2 + "'")


'Example of eliminating the need for the Access function Nz()

strSQL = strSQL & " And OnlyPositiveNumericField3 > 0" & Forms("frmSomeForm")!txtTextBoxOnlyPositiveNumericField3

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:

CODE

Dim x As Long

x = Nz(DLookup("SomeValue","SomeTable", "SomeCriteria"),0)

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]) & ""), "", [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:

CODE

Public Sub SomeProcedure()

   Dim varTemp As Variant

   varTemp = Len(Me.txtTextBox1)

   If varTemp Then
       'Do Something
   ElseIf Not varTemp Then
       'Do Something Else
   Else
       'Do yet another thing
   End If

End Sub

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

CODE

=IIf(Len(Me.txtTextBox1), truepart, falsepart)

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

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/forum/Letters-variable-address-t1178235.html&p=1178396#entry1178396

strive4peace[current year]: http://www.utteraccess.com/forum/omit-blanks-search-param-t1548698.html&p=1549569#entry1549569

LPurvis: http://www.utteraccess.com/forum/Text-Box-Remove-Unwante-t1543944.html&p=1544046#entry1544046

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/showthread.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!}

Edit Discussion
Custom Search
Thank you for your support!
This page has been accessed 11,688 times.  This page was last modified 04:54, 4 February 2012 by Jack Leach. Contributions by Brent Spaulding  Disclaimers