L
L.A. Lawyer
Can anyone explain the difference between these and when to use each,
particularly in conditional statements?
particularly in conditional statements?
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
P
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.
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.
A
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
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
D
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.
NULL, if you DELETE the value the value in the field because a
zero-length string as opposed to NULL.
L
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?
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?
D
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.
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.
A
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
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
D
Dirk Goldgar
L.A. Lawyer said: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
J
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.
the field. Otherwise when you delete the value, null is stored in the field.