'Serving the Microsoft® Access
Community since 1997'

Free Samples
About Us



Null Values

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


Are you ready to cash in on the multi-billion dollar
Health and Wellness industry? Let us handle the
research and hard work for you...

"How to Start Your Own Home-Based
 Business, Or Boost Your Existing Business,
with Private Label Resale Rights to
Over $1,000 Worth of Freshly Written
Health and Fitness Products and
Content Each and Every Month"

Low Carb Dieting, Hair Loss Prevention,
Arthritis Relief, Muscle Building Secrets,
Sunless Tanning Guide, Healthy Dating Tips,
How to 'Spot Tone' Your Way to A Beautiful Body...

And more of the hottest Health and Fitness topics
for selling and distributing both online and off!

Click Here to Get Started Within Minutes!

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, 2008 Richard W. Killey. All Rights Reserved. - Privacy Policy