ACCESS - Zero Length versus null values

Posted on 2011-10-06
Last Modified: 2012-05-12
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.
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

    LVL 56

    Expert Comment

    by:Jim Dettman (Microsoft MVP/ EE MVE)

     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.

    LVL 47

    Expert Comment

    by:Dale Fye (Access MVP)
    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

    LVL 75

    Expert Comment

    by:DatabaseMX (Joe Anderson - Access MVP)
    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:
    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.
    LVL 26

    Expert Comment

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


    LVL 47

    Expert Comment

    by:Dale Fye (Access MVP)

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

    Expert Comment


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

    Expert Comment

    by:Dale Fye (Access MVP)

    That is good to know.  Thanks for the link.
    LVL 56

    Expert Comment

    by:Jim Dettman (Microsoft MVP/ EE MVE)
    <<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.

    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

    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.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Top 6 Sources for Identifying Threat Actor TTPs

    Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

    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.

    760 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now