Difference Between Null, Empty and ""

[L.A. Lawyer]

Can anyone explain the difference between these and when to use each,
particularly in conditional statements?

 

[Paul Overway]

Null represents the absence of any value (i.e., no record, or no field
value)...and can only be assigned to a Variant data type. Empty indicates
that a Variant type variable has been declared, but a value has not been
assigned....its basically a default assignment. "" represents an empty
string. Each indicates no value, but in a different context. Logically,
you have to code differently to test for the condition. Or you might use
something like this...

Function IsNothing(varArg As Variant) As Boolean
'Checks whether argument is Null, Empty, empty string, or Nothing

On Error GoTo Err_IsNothing

IsNothing = False

Select Case VarType(varArg)
Case vbEmpty
IsNothing = True
Case vbNull
IsNothing = True
Case vbString
If Len(varArg) = 0 Then
IsNothing = True
End If
Case vbObject
If varArg Is Nothing Then
IsNothing = True
End If
End Select

Exit Function

Err_IsNothing:
IsNothing = True
End Function

If the data type of the variable being passed is something other than
Variant, String, or an Object, there is not much point in using the
function. For example, passing any Integer variable would also result in
False because an Integer is 0 by default...therefore, it has a value.

 

[Allen Browne]

Paul has given you a good answer. I'll add an example.

If you have a table of clients with phone numbers. You don't know everyone's
numbers, so some fields are null until you find out. Then you learn that one
of the students has no phone. The number is not unknown (null); it is known
to be non-existent. You could record that in the database as a zero-length
string (zls).

To the user, there is no visible difference between null and a zls, so
trying to create that kind of distinction generally does little more than
confuse the users. I strongly suggest that you therefore set the
AllowZeroLength property to No for all text fields in your tables, so you
don't accidentally end up with a ZLS where you intended a Null. If you allow
ZLS, you every query and every piece of code must check for both
cases--clumsy, inefficient, and error-prone.

For more info, see:
Nulls: Do I need them?
at:
http://allenbrowne.com/casu-11.html

 

[David C. Holley]

There is also that once a value is put into a field, that was previously
NULL, if you DELETE the value the value in the field because a
zero-length string as opposed to NULL.

 

[L.A. Lawyer]

Ok, if this all true, do I need to always test if the null status and the ""
status of a field to verify that it contains no data? It is a little time
consuming to keep doing multiple conditional statements. In other words,
for example, if I test for "", is that enough to catch the null and empty
values too?

 

[David C. Holley]

No. Testing for "" (a zero-length) string will not catch values that are
NULL. One option would be to create a function that tests for both as in

function isValidLength(varValue as Variant) as boolean

isValidLength = False
if IsNull(varValue) = False AND Length(varValue) > 0 then
isValidLength = True
end if

end function

The function was written off the top of my head, so it may need some
tweaking.

 

[Allen Browne]

Agreed that this is just wasteful. You can avoid that by setting the
AllowZeroLength property to No for all Text fields, Memo fields, and
Hyperlink fields throughout your application.

Testing for a zls alone (i.e. if it is equal to "") will NOT work.

You do not need to test for Empty in a field. That does not apply to
fields--only to Variants in VBA code, where you declared a variant but did
not assign anything to it.

The code below will loop through all fields in all tables of your database
and set the AllowZeroLength field to No, listing the fields that were
changed. If you already have data in the tables you would also need to run
an update query on those fields to find any records that already contain the
ZLS.

Function FixZLS()
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field
Dim prp As DAO.Property
Const conPropName = "AllowZeroLength"
Const conPropValue = False

Set db = CurrentDb()
For Each tdf In db.TableDefs
If (tdf.Attributes And dbSystemObject) = 0 Then
For Each fld In tdf.Fields
If fld.Properties(conPropName) Then
Debug.Print tdf.Name & "." & fld.Name
fld.Properties(conPropName) = conPropValue
End If
Next
End If
Next
End Function

 

[Dirk Goldgar]

Ok, if this all true, do I need to always test if the null status and
the "" status of a field to verify that it contains no data? It is a
little time consuming to keep doing multiple conditional statements.
In other words, for example, if I test for "", is that enough to
catch the null and empty values too?

As Allen says, if you don't allow the field to contain a ZLS, this
problemn doesn't arise. If you want to write code that tests whether a
field is Null or a ZLS, and you don't care which, you can do it in one
simple test, like this:

If Len(YourField & vbNullString) = 0 Then
' The field is either Null or a zero-length string
End If

This works because of a special characteristic of the concatenation
operator '&' -- it converts a Null being concatenated into a zero-length
string.

If you want to include the possibility that the field contains only some
number of spaces (not easy to get, in Access), then you can modify the
above to

If Len(Trim(YourField & vbNullString)) = 0 Then
' The field is either Null, spaces, or a zero-length string
End If

 

[John Spencer (MVP)]

The value becomes a zero length string only if you allow zero-length strings in
the field. Otherwise when you delete the value, null is stored in the field.