Nulls and Zero-Length Strings

Relational databases support a special value in fields, called a Null, that indicates an unknown value. In contrast, you can set Text and Memo fields to a zero-length string to indicate that the value of a field is known but the field is empty.

Why is it important to differentiate Nulls (unknown values) from zero-length strings? Here's an example: Suppose you have a database that stores the results of a survey about automobile preferences. For questionnaires on which there is no response to a color-preference question, it is appropriate to store a Null. You don't want to match responses based on an unknown response, and you don't want to include the row in calculating totals or averages. On the other hand, some people might have responded "I don't care" for a color preference. In this case, you have a known "no preference" answer, and a zero-length string is appropriate. You can match all "I don't care" responses and include the responses in totals and averages.

Another example might be fax numbers in a customer database. If you store a Null, it means that you don't know whether the customer has a fax number. If you store a zero-length string, you know the customer has no fax number. Access 2010 gives you the flexibility to deal with both types of "empty" values.

You can join tables on zero-length strings, and two zero-length strings will compare to be equal. However, for Text, Memo, and Hyperlink fields, you must set the Allow Zero Length property to Yes to allow users to enter zero-length strings. (Yes became the default value in Microsoft Access 2002.) Otherwise, Access converts a zero-length or all-blank string to a Null before storing the value. If you also set the Required property of the Text field to Yes, Access stores a zero-length string if the user enters either "" (two double quotes with no space) or blanks in the field.

Nulls have special properties. A Null value cannot be equal to any other value, not even to another Null. This means you cannot join (link) two tables on Null values. Also, the question "Is A equal to B?" when A, B, or both A and B contain a Null, can never be answered "Yes." The answer, literally, is "I don't know." Likewise, the answer to the question "Is A not equal to B?" is also "I don't know." Finally, Null values do not participate in aggregate calculations involving such functions as Sum or Avg. You can test a value to determine whether it is a Null by comparing it to the special keyword NULL or by using the IsNull built-in function.