NULL v. Zero Length String
What's the difference between a NULL value and a Zero Length String
Q: What is the difference between a NULL value and a
Zero Length String?
NEW: Check out my new video: Zero Length Strings
A: The difference between a
NULL and a Zero-Length String (ZLS) is that a NULL has no value, whereas a ZLS has been specifically given a value of
blank. It's basically the difference between saying, "no value" or "no
value YET."
Furthermore, this distinction usually only comes about in Access
when you're dealing with VBA programming and you either set the ZLS
value yourself, or return the value as part of a function call, such as:
LastName = ""
FirstName = NULL
When you're entering data in a table or form, you will usually either specify
some data, or no data. Your fields will either have a value, or they
will be NULL. Even if you type in a value and then come back later and
delete it, it will still be NULL. Manually deleting data from a table
sets it to NULL, so the average user will never really have to worry
about what a ZLS is.
However, let's say you're creating a database where you need to know
more specific information on people. Let's say you're tracking first,
middle, and last names, and you NEED to know whether or not someone has
a middle name. You could set their middle name equal to a ZLS to
indicate that they have NO middle name, as opposed to not knowing what
their middle name is. Statistically it could make a difference. You
could make a button, for example, that just says:
MiddleName=""
You can check for ZLS by looking for "" - an empty string. You can check
for NULL by using the IsNull function. In your VBA code this will often
result in looking for both:
If IsNull(LastName) or
LastName="" Then
' Do some stuff
End if
You can prevent ZLS in your table by changing the Allow Zero Length
property of a text or memo field to No/False. This will prevent any ZLS
values from getting in your table.
Do you need to worry about any of this? For the most part, no. The
difference between NULL and ZLS is mostly theoretical, and unless you're
specifically coding your database to CARE about the difference between
the two, you shouldn't have to worry about it. However, because some
functions might return a ZLS, you might want to check for both in your
VBA comparisons. Usually this distinction only matters when you need to
care about the difference between NO value and no value YET.
Also, Access will index NULL values and ZLS values differently, which is
a whole different topic of discussion.
By Richard Rost
Click here to sign up for more FREE tips
|
