ACCESS - Zero Length versus null values

 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.

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

Select allOpen in new window

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:

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.

LOL
Danger!
For fun work your way all the way through this
https://www.experts-exchange.com/questions/27306297/Avoiding-Nulls.html?cid=748&anchorAnswerId=36589557#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?

Nick,

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

@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...

Nick,

That is good to know.  Thanks for the link.

<<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.

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
https://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.