It does not take long for a new Access user to "bump" into the term Null. As you created your first table you should have noticed in the bottom half of the table design area a variety of properties for text fields. Three (3) important ones are:

• Required
• Allow Zero Length
• Indexed

(article continues after sponsor spot)

That "Allow Zero Length" is another way of saying Null.

In fact, create a simple table with a text field for a primary key. Now try to add a new record without typing a value for the primary key. Access will complain about the key being Null. Try the same by creating a plain text field (not a key) and making the Allow Zero Length property No and the Required property Yes. Now create a record with no value for that field.

Null (in my dictionary) is defined as "not any, zero". Access, however, says it is an unknown value, which is not the same as zero. Try this in Access VBA, with all variables declared as variant type.

X = Null
Y = 4
A = X + Y

The result for A will be Null. The total of the sum is unknown as one of the parts is unknown. To get around this problem, you can do it this way.

A = nz(X,0) + nz(Y,0)

This time the value of A will be 4, since the nz() function makes the variable the default value (0 in this case) if the original value is Null.

For an interesting article about the negative features of Null, and its place (or lack of it) in the Relational Model, visit Database Design Resource (a new window will open).

the following is an advertisement for a new collection of Health and Wellness Information products being promoted by our sister site at www.nutritionlessons.com

Happy Coding

 
Note: This web site dedicated to MS Access database users is an independent publication of Richard W. Killey and is not affiliated with, nor has it been authorized, sponsored, or otherwise approved by Microsoft® Corporation.
 

© 2006, 2007 Richard W. Killey. All Rights Reserved.