Solved

# ACCESS - Zero Length versus null values

Posted on 2011-10-06
856 Views
Can someone explain in plain English the difference between Null values and zero length values?
Examples would be useful as well.  There's a lot of theoretical explanation on the web but I haven't found any examples.

ANALOGIES would be useful too.
0
Question by:brothertruffle880

LVL 56

Accepted Solution

A null is the absense of any value.  Somewhat practicle example; recording someone's middle name:

They call in over the phone and ask for XYZ newsletter.  You hang up and realize you didn't ask for their middle initial.  So the value is unknown and would be NULL.

Next call you remember to ask and they say "I don't have one".   What's known is there is not one, so a zero-length string would be in order.

More practical, some like to use zero length strings to indicate an unknown as it saves you from doing this everywhere:

If NZ(<value>,"") <>"" then

Jim.
0

LVL 56

Expert Comment

I should have also mentioned that some like to avoid NULLs because of NULL propagation.  Depending on what your doing, a NULL may propagate through a expression (like putting strings together or summing some fields) and give you a NULL as a result.

That's why many just use a zero length string or 0.   Problem with a zero and numerics is that 0 is very often a valid value (ie. Outside temp), so you can't use it to indicate that a value is unknown.

Many then go on to use a dummy value to indicate something unknown, like -999999.  Problem with that is if your asked "What was the average temp over the last 30 days?" you really don't want to include an "unknown" of -999999.

Jim.
0

LVL 47

Expert Comment

A NULL represents an unknown value.  If no information has been entered in a field, the value is unknown and is represented by a NULL value.  The thing about NULLs is that when you compare a NULL to anything else you also get an unknown value because you don't know whether a field that contains a NULL is "Yes" or "David".

A zero length string is just that, it is a string that has no value.  As an example, if you enter a value in a field, and save the record, then come back to the record and delete the value, and save the record, what will generally get saved is a zero length string (if the field is a text or memo field).

The problem is that for comparison sake they may both represent unknown values but you cannot simply use the ISNULL() or NZ() functions or the IS NULL expression in a query to identify records where there is no value (NULL or "") in a particular field.  These three methods will identify NULLs, but not zero length strings.

To identify NULL values and zero length strings, you can use something like:

LEN([fieldname] & "") > 0

or

[FieldName] & "" = ""

I actually have a function IsNullorBlank() which I include in my library, so when I want to test for a field or control being NULL or an empty string, I just use:

IF IsNullOrBlank([Fieldname]) Then

The function looks like:
``````Public Function IsNullOrBlank(SomeValue as Variant) as boolean

IsNullOrBlank = (LEN(TRIM(SomeValue & "")) = 0)

End Sub
``````
0

LVL 75

Expert Comment

Avoid Zero Length String:

http://allenbrowne.com/bug-09.html

scroll down to Zero Length String

=====

Table fields created in Access 97 had their Allow Zero Length property set to No by default. In Access 2000 and later, the property defaults to Yes, and you must remember to turn it off every time you add a field to a table.

To the end user, there is no visible difference between a zero-length string (ZLS) and a Null, and the distinction should not be forced upon them. The average Access developer has enough trouble validating Nulls without having to handle the ZLS/Null distinction as well in every event procedure of their application. The savvy developer uses engine-level validation wherever possible, and permits a ZLS only in rare and specific circumstances.

There is no justification for having this property on by default. There is no justification for the inconsistency with previous versions.

Even Access itself gets the distinction between Null and ZLS wrong: DLookup() returns Null when it should yield a ZLS.

You must therefore set this property for every field in the database where you do not wish to explicitly permit a ZLS. To save you doing so manually, this code loops through all your tables, and sets the property for each field:
0

LVL 61

Expert Comment

Another point about nulls vs empty (zero-length) strings is that an empty string can be compared to another empty string (they are the same).  Nulls on the other hand are nothing at all.  They can't be compared to anything, they are not even equal to other Nulls.
0

LVL 26

Expert Comment

LOL
Danger!
For fun work your way all the way through this
http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_27306297.html?cid=748#a36589557

Now,
The empty-string is just that --> ""
It has a datatype, string, and it has a value "", or no characters
You can see if something is equal to empty-string
If MyString = "" then
You can concatenate empty-string to stuff
MyString = MyString & ""

Null, on the other hand, is the absense of a value.  It is unknown
When you leave a field empty, it's value is null
When you do a Left Join, those values that don't exist in the one table are null.

Any datatype can be null, only text and memo can be empty string
Because null is unknown (it could be anything, everything or nothing) you can't use operators with it
1 + null = null
1 * null = null
"something" & null = null.
The only exception to null operations yielding null is in boolean operators, and those only because they have two cases
True OR (True or false doesn't matter) = True -- because you have one true, and only need one (the very definition of OR)
so, True OR Null = True
Similarly
False AND (True or false doesn't matter) = False-- because you have one false, and only need one (the very definition of AND)
So False AND Null = False
Everything else, when you bring null into the equation your result is null
You cannot test to see if something is equal to null--because null can be anything, everything or nothing

Null must be handled.  Empty-string does not need to be handled
You test for null with Nz() and use it to replace it
Nz(SomeString,"")
You can test to see if something is null with IsNull()
if IsNull(SomeControl) = True then ....

Questions?

0

LVL 47

Expert Comment

Nick,

You are wrong with "Something" & NULL = NULL, it actually yields "Something"
0

LVL 26

Expert Comment

@fyed,

I tested it, you are right. However...
That's a highly dangerous habit to be in
http://msdn.microsoft.com/en-us/library/ms176056.aspx
as the accepted result of "something" & null = null
And perhaps someday soon it may bite you...
0

LVL 47

Expert Comment

Nick,

That is good to know.  Thanks for the link.
0

LVL 56

Expert Comment

<<You are wrong with "Something" & NULL = NULL, it actually yields "Something" >>

Old trick:

"Something" + NULL = NULL

and maybe that's what Nick was thinking of.

Jim.
0

LVL 26

Expert Comment

I didn't want to confuse the OP, and I am aware of what + does as a string concatenator in Access
@harfang has an excellent article on that here
http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_2040-Concatenation-in-Access-Reports-and-Forms.html?sfQueryTermInfo=1+30+concaten+harfang

But no, I was under the belief, and most languages treat, "Something" & Null = null, where & is the string concatenator
(Let's not talk about Oracle and zero length strings)
I was surprised that in VBA Null & "Something" = "Something"

I wouldn't count on that continuing to work though, as the next version of SQL Server is NOT going to support that--and I suspect the next version of Access won't either.
0

## Featured Post

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.