It s About Nothing: Null Values and Zero-Length Strings

It's About Nothing: Null Values and Zero-Length Strings

At a press conference in 2003, Donald Rumsfeld, Secretary of Defense in the Bush Administration, made the following statement:

Reports that say something hasn't happened are always interesting to me because, as we know, there are known knowns, there are things we know we know. We also know there are known unknowns; that is to say, we know there are some things we do not know. But there are also unknown unknownsthe ones we don't know we don't know.

No doubt unknowingly, Mr. Rumsfeld has provided us with a superb introduction to the topic of nulls and zero-length strings.

Thus far, we've been dealing with values that are "something"Helen, coffee, 378, 10/11/97, and so on. But what about situations in which we don't have any "something"? Telling Access that we don't have anything to put into a tableand just why we don't have a "something" to put inis, in some ways, as important as storing those "somethings."

Let's look at a concrete example. The Acme Company makes widgets. It has a database with a Customers table that has contact info, including a field for the fax number. Table 1.4 presents a few facts about four of its customers.

Table 1.4. Customer Facts

Company Name	Has Fax Machine/Software?	Fax Number
Williams Brothers	Yes	213-555-3678
Dalton Inc.	No	Does not exist
Hellman International	Yes	Exists, but we don't know it
Morgan Company	Don't know	Don't know if there's one

The fax number for Williams Brothers causes no problems. It is a known knownwe know there's a fax number, and we know the number. We can simply enter the number in the fax number field.

But what values should we enter in the fax number field for the other companies?

Zero-Length Strings

For Dalton Inc., we know they don't have a fax. Because we know they don't have a fax, we also know there is no fax number. It's also a known known. We can tell Access that we know there is no fax number by entering a zero-length string, represented by two double-quotation marks with no space between them ("").

NOTE

Only Text, Memo, and hyperlink fields can accept zero-length strings.

Null Values

Next is Hellman International. We know they have a fax machine. We also know that we don't know the fax number. We have a known unknown. In this case, the fax number is merely missing. Perhaps Acme's clerk forgot to ask Hellman what the fax number is or deleted it by accident. In this case, we would use a null value; we would just leave the field blank. A null value isn't 0; it isn't a "we know it doesn't exist." It's just nothing, empty, zilch.

Finally, there's Morgan Company. We don't know whether the company has a fax. So we don't know whether Morgan Company has a fax number. But we don't know whether we don't know the fax number because the company might not have a fax number at all. It's an unknown unknown. In these cases, we would also use a null value, a blank.

That's enough to get us started on null values and zero-length strings. You'll learn more about them in Chapter 5.