Checking for Null or zero-length

[Amit]

Hi,

This is a general question regarding checking a textfield
for Null or zero-length.

I've seen instances of code like this:
a. If (IsNull(Me.txtName)) or (Len(Me.txtName) = 0)
Then ...

b. If (Len(Me.txtName & "") = 0) Then ...

I was wondering if there are situations where a. is
preferred over b., and vice versa, or can either be used
interchangeably?

Comments?

-Amit

 

[Marshall Barton]

This is a general question regarding checking a textfield
for Null or zero-length.

I've seen instances of code like this:
a. If (IsNull(Me.txtName)) or (Len(Me.txtName) = 0)
Then ...

b. If (Len(Me.txtName & "") = 0) Then ...

I was wondering if there are situations where a. is
preferred over b., and vice versa, or can either be used
interchangeably?

There's a million way to check for this situation. Here's
another popular one:
If Nz(Me.txtName, "") = "" Then

Which one you choose is more a matter of coding style than
anything else. But I think your b. is commonly believed to
be a fraction of a microsecond faster than other ways.

 

[Douglas J. Steele]

In my opinion, the second is always preferable, since it involves fewer
operations. Other than that, there's no real difference between them.

 

[Allen Browne]

To add to the other comments, the fastest and safest approach is to set
Allow Zero Length to No for all your fields, except the 0.01% of fields
where you actually need it.

That approach simplifies your code. You only can just write:
If IsNull(Me.txtName) Then

It also avoids confusing the user, who cannot tell the difference between a
Null and a zero-length-string (ZLS).

It also avoids confusing Access, which cannot tell the difference between a
Null and a ZLS either. Try DLookup().

You did not ask about queries, but if you do allow ZLS, in your criteria
use:
WHERE (MyField Is Null) OR (MyField = "")
so that JET can evaluate the expression without calling the IsNull()
function.

 

[Fred Boer]

Dear Allen:

Your ""0.01%" comment caught my eye and sent me scurrying to check the text
field properties in my tables. I find I have zero length strings allowed and
disallowed with thoughtless and carefree abandon! <g>

I can easily change this property for all of my text fields, but I am
curious... Could you describe a situation which might correspond to the
"0.01%"?

Thanks!
Fred Boer

 

[Allen Browne]

Hi Fred

You may want to permit a ZLS in a field that is part of a unique index.

Example: database dealing with shops/units in a shopping/commercial centre,
where there are units such as 7A, 7B, 11, ... You do not want 11 to sort
between 1 and 2, so you use two fields: Unit (number) and Suffix (Text). The
combination must be unique. Set these properties for Suffix:
Allow Zero Length No.
Required Yes (i.e. Null is not permitted)
Default Value ""

This is a value use for the ZLS: you are denying the user the possibly of
entering something where the suffix is unknown (Null not permitted), but
accepting the entry of the unit is known to have no suffix (i.e. ZLS).

This combination of properties permits the unique index we want, but there
is no user-confusion between Null and ZLS, since only one is possible, and
the Default Value provides the ZLS if the user does not want to enter
something.

These are quite rare, and normally we run code to turn off Allow Zero Length
for all fields when we create a database:

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
Debug.Print tdf.Name
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

Set prp = Nothing
Set fld = Nothing
Set tdf = Nothing
Set db = Nothing
End Function

 

[Fred Boer]

Dear Allen:

Thank you very much for your helpful answer! Just a couple of things:

1. I assume that you meant to say "Yes" not "No" for the value of the "Allow
Zero Length" property, right?

Allow Zero Length No.
Required Yes (i.e. Null is not permitted)
Default Value ""

2. Thanks for the code...it saved me a lot of time!
3. Given that ZLS should, as you say, be set to "NO" 99.9% of the time...
perhaps it should be suggested to Microsoft that that this be the default
property setting for text fields..

Thanks again!
Fred

P.S. ...and change the default setting of numeric fields to Null!

 

[Allen Browne]

Yes. Should have been Yes, not No. (I'm always setting the thing to No!)

We have certainly provided feedback to MS about the problem properties.
IMHO, the only way they can now keep everyone happy (after some people
expect the property to default to No and others expect it to default to
Yes), is to give the user a configuration option where you can set the
default you want.

Agree completely that the Default Value for numeric fields should be Null.

 

[Amit]

Thanks everyone!!

-Amit

-----Original Message-----



There's a million way to check for this situation. Here's
another popular one:
If Nz(Me.txtName, "") = "" Then

Which one you choose is more a matter of coding style than
anything else. But I think your b. is commonly believed to
be a fraction of a microsecond faster than other ways.