[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 915
  • Last Modified:

ACCESS - Zero Length versus null values

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.
  • 3
  • 3
  • 3
  • +2
1 Solution
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
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 Dettman (Microsoft MVP/ EE MVE)PresidentCommented:

 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.

Dale FyeCommented:
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


[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

Open in new window

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Avoid Zero Length String:


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:
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.
For fun work your way all the way through this

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
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
You can test to see if something is null with IsNull()
if IsNull(SomeControl) = True then ....


Dale FyeCommented:

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

I tested it, you are right. However...
That's a highly dangerous habit to be in
as the accepted result of "something" & null = null
And perhaps someday soon it may bite you...
Dale FyeCommented:

That is good to know.  Thanks for the link.
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
<<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.

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

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.

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 3
  • 3
  • 3
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now