Tables, Table Design

Nulls and Zero-Length Strings

Edit from Relationships Window

Nulls and Zero-Length Strings (V2 and later)

Relational databases support a special value in fields, called a Null, that indicates

an unknown value. 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 test "A = B," when A, B, or both A and B

contain a Null, can never yield a true result. 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.

In contrast, you can set Text or Memo fields to a zero-length string to indicate

that the value of a field is known but the field is empty. 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. Other-

wise, 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 "" or blanks in the field.

Why is it important to differentiate Nulls 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 "nothing" 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 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 gives you the flexibility to deal with both types of "empty" values.

Top

Edit From Relationships Window (Access 97 and later)

You can right-click any table in the Relationships window and then choose Table

Design from the shortcut menu to open that table in Design view. You can also

choose Print Relationships (Access 2000) from the File menu while viewing the

Relationships window to create a report that prints what you have laid out in the window.

Top