Microsoft Access Database: How to Work with Nulls in Your Access database

Microsoft Access – Nulls: Do We Need Them?

The following three keywords are the most misunderstood when working with an Access Database:

  1. Null
  2. Empty
  3. Nothing

To answer the question; YES we need them; there are all important and you might as well master these operators and where it will be found in your Access database.

There is a definition of the word ‘Null’

Null - definition of the word

Source: www.thefreedictionary.com/null

But this doesn’t really help as it keeps referring to the ‘Nothing‘ and ‘Zero‘ which mean different things in an Access database.

New users in particular find Null confusing and want to know how they should prevent or handle them in the first place. In fact, there are important and you shouldn’t want to prevent them at all.

A Null value is another way of saying it is ‘Unknown‘ or  has ‘No Entry of Data‘ which can be the case if when entering records you miss some of the fields. For example, in a Microsoft Access table or form if you entered only the first name and not the surname to a text field then the surname field is null and not a zero length string of characters. The same could be said if you neglected to enter a date of birth; that field is null too.

This becomes more relevant when dealing as a relational database (joining tables and queries together). In fact, you will be relying on null values quite a bit.

At the more advanced level when coding using the VBA programming language, null values play an important role here too and it is essential to handle known values across all entities as if one is missing (oops sorry unknown) then it returns null!

In Microsoft Access generally, it’s tempting to say a null value is the same as a zero length value which in fact it is not. This refers to text fields where a zero length means value that has no value (an empty string) as opposed to it being unknown (not populated at all). There is no visual difference between the two and can be confusing to all levels of Access users. Different versions of Microsoft Access changed the default setting of ‘Allow Zero Length‘ between ‘No’ and ‘Yes’ and personally this should set (or left as ‘No’).

Finally, null is not the same as 0 (zero). This is simpler to understand as a zero is a real value and not an empty, nothing or unknown value!

This entry was posted in Database Theory, MS Access, Tables, VBA and tagged , , , . Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>