Somethin' as Nuthin'

  • Thread starter Thread starter Tom P.
  • Start date Start date
T

Tom P.

Hello,

I'm trying to change the graphic of a tab using the
standard Access 2002 Tab Control based on whether or not
the memo field on the tab contains any text.

Displaying/changing the graphic is not difficult nor the
issue. The problem is what function should be used to
determine if the field contains no text?

Let me explain...
In the "On Current" event for the form I have a MsgBox
set to display:
Stuff in field on the form: content2_text.Value
IsNull: IsNull(content2_text.Value)
IsEmpty: IsEmpty(content2_text.Value)
Chars >0: (Len(content2.Value) > 0)
Chars: Len(content2.Value)

Much to my surprise, I get results such as these:
Stuff in field on the form:
IsNull: True
IsEmpty: False
Chars >0:
Chars:

Stuff in field on the form:
IsNull: False
IsEmpty: False
Chars >0:
Chars:

Even though there is not text in the field, VBA still
seems to think there is.

However, this seems to work consistently!?:
IsNull(Len(content2.Value) = "")

What am I missing, besides a clue? ;-)

Thanks,
Tom
 
Tom, this is an important question.

The value Empty refers to a VBA variable of type Variant that has not been
assigned to anything. Empty behaves like a zero-length string and like the
numeric value zero, depending what you compare it to. It does not apply to
fields.

The value Null refers to a field that has no content. Therefore IsNull() is
the correct test in VBA for what you are seeking to do.

Think of Null as meaning Unknown. That explains why nulls propagate the way
they do. If you ask:
? 3 + Null
you are asking how much is 3 plus unknown. VBA says the answer is Unknown,
i.e. it returns Null. Likewise, when you ask:
? Len(Null)
VBA says, "I have no idea how long something is if it's Unknown: the answer
is Unknown.

This means that there are 3 possible outcomes for testing the truth value of
something in VBA: True, False or Null (not known). For more information on
how to work comparisons involving Nulls, see:
Common Errors with Null
at:
http://allenbrowne.com/casu-12.html
 
Thanks Allen.

A lot of good information.

LOL, sometimes it responds correctly to one but not the
other and vice versa. One function will not consistently
return a value of True when there is nothing in the field.

So, I am reminded of a lesson I learned from Bugs Bunny
early one Saturday morning...
If you can't beat 'em, Join 'em!

The code below seems to work pretty effectively when
checking for an empty field...

If IsNull(content2.Value) Or content2.Value = "" Then
SendSpam("(e-mail address removed)")
End If


Regards,
Tom P.
 
Okay. Makes sense.

You can avoid the 2nd argument if you turn off the Allow Zero Length
property for all Text fields in your database. IMHO, that property should be
off by default: it's way to subtle for most users to understand the
difference, and even Access itself gets it wrong (e.g. DLookup() wrongly
returns Null if the field contains a zero-length string).

If you want to do that, this function will do it for all non-system tables
in your database. If it barfs on existing data, use an Update query statment
to update "" to Null.

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 = DBEngine(0)(0)
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) = False
End If
Next
End If
Next

Set prp = Nothing
Set fld = Nothing
Set tdf = Nothing
Set db = Nothing

End Function
 
To test for either Null or an empty string ...

If Len(SomeValue & vbNullString) > 0 Then

If the value is Null, or if it is a number or a date, the concatenation of
vbNullString will result in a string, which can then be tested with the
Len() function. And as an empty string has no length, if the value is not
Null, adding an empty string will not increase its length.
 
Back
Top