Zero-length strings

[Bill]

How do I set a text field in a table to a zero-length
string? MyField="" causes the runtime error:

"Field MyField cannot be a zero-length string"

Both Null and Empty refer to variant type variables,
so unless I assign a null variant variable to the text
field I don't know how to do it directly.

 

[Duane Hookom]

My question would be why? However, have you set the field properties in
table design to allow zero length strings?

 

[Bill]

I don't understand your question "why". A table's text field either
has text assigned to it or it doesn't. If a subsequent action on the
part of the user is to obliterate whatever was in that field, how
else would that be done other than to make the field essentially
empty. But, since empty and null are attributes of variant type
fields, then there's no other choice that I'm aware of other than
to allow the text field to be zero-length.

I wasn't familiar with the field properties option to allow zero-
length fields, but now I am.

Thank you,
Bill

 

[Dirk Goldgar]

I don't understand your question "why". A table's text field either
has text assigned to it or it doesn't. If a subsequent action on the
part of the user is to obliterate whatever was in that field, how
else would that be done other than to make the field essentially
empty. But, since empty and null are attributes of variant type
fields, then there's no other choice that I'm aware of other than
to allow the text field to be zero-length.

Bill, *every* field in an Access table, with the exception of Boolean
(Yes/No) fields, can hold the value (or, more accurately, non-value)
Null. So text fields can be set to Null, and that is usually the way in
which "no text assigned" is represented.

From a database perspective, there's a significant difference between
Null and "". Consider a text field intended to hold a person's middle
name. A value of Null in this field might be interpreted to mean, "we
don't know what this person't middle name is." A value of "", on the
other hand, might be interpreted to mean, "we know that this person
doesn't have a middle name." This distinction may or may not be
important, depending on the application, but it's important for the
developer to know that the distinction can be made.

In those cases where there is no distinction to be made between a Null
value in a field and a zero-length string in that field -- and that is
most cases in my experience -- it's generally a good idea to disallow
zero-length strings in text fields. That way, you don't need to code
for the distinction; when you want to know whether the field is empty,
you can just check whether the field is Null or not. That simplifies
your coding.

 

[User]

Bill, *every* field in an Access table, with the exception of Boolean
(Yes/No) fields, can hold the value (or, more accurately, non-value)
Null. So text fields can be set to Null, and that is usually the way in
which "no text assigned" is represented.

From a database perspective, there's a significant difference between
Null and "". Consider a text field intended to hold a person's middle
name. A value of Null in this field might be interpreted to mean, "we
don't know what this person't middle name is." A value of "", on the
other hand, might be interpreted to mean, "we know that this person
doesn't have a middle name." This distinction may or may not be
important, depending on the application, but it's important for the
developer to know that the distinction can be made.

In those cases where there is no distinction to be made between a Null
value in a field and a zero-length string in that field -- and that is
most cases in my experience -- it's generally a good idea to disallow
zero-length strings in text fields. That way, you don't need to code
for the distinction; when you want to know whether the field is empty,
you can just check whether the field is Null or not. That simplifies
your coding.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

Good explanation, DG.
However, there are database "experts" out there who froth at the mouth
whenever one mentions field values of Null.
Some will even have epileptic fits when you call it a field instead of a
column.

 

[Dirk Goldgar]

Good explanation, DG.
Thanks.

However, there are database "experts" out there who froth at the mouth
whenever one mentions field values of Null.

That doesn't make much sense.

Some will even have epileptic fits when you call it a field instead
of a column.

That makes more sense, but it's a pointless argument, in my view. When
talking about Access, one may as well use the specific terminology
Microsoft uses.

 

[Bill]

Dirk, I understand your thinking regarding null versus zero-length
strings and what the implied meaning might be amongst the
development community.

Though I might not draw any inferrence between the two, my own
case at hand might very well be served if such inferrences were drawn.
Notwithstanding all of that, I am now puzzled because I've had several
situations wherein I got runtime and/or compiler errors dealing with the
use of Null in the context of string handling. Yet, this thread led me to
again attempt to make such an assignment and did so without difficulty.

My understanding of the use of Null seems to be flawed, as I was led
to believe it was reserved for variables of type variant. The VBA Help
text definition of Null makes no such claims, yet most of the "Null
functions" indicate the argument expressions are of type variant. Can
you clear this up for me once and for all?

Thanks,
Bill

 

[Dirk Goldgar]

Dirk, I understand your thinking regarding null versus zero-length
strings and what the implied meaning might be amongst the
development community.

Though I might not draw any inferrence between the two, my own
case at hand might very well be served if such inferrences were drawn.
Notwithstanding all of that, I am now puzzled because I've had several
situations wherein I got runtime and/or compiler errors dealing with
the use of Null in the context of string handling. Yet, this thread
led me to again attempt to make such an assignment and did so without
difficulty.

My understanding of the use of Null seems to be flawed, as I was led
to believe it was reserved for variables of type variant. The VBA Help
text definition of Null makes no such claims, yet most of the "Null
functions" indicate the argument expressions are of type variant. Can
you clear this up for me once and for all?

I suspect you are failing to distinguish between field and control
values, on the one hand, and VBA variables on the other. A field in a
table or recordset, or any of the Access controls that can be bound to a
field, can have a Null value (except for boolean fields, which are
always either True or False no matter what you do). These are *not*
variables, in the sense that VBA uses. A VBA variable is a programming
element that is declared in VBA code using the Dim, ReDim, Public, or
Private keywords (and not Const). Every VBA variable has a data type,
and of those data types, only the Variant type can hold a Null.

So, of these variables,

Dim strA As String
Dim lngB As Long
Dim dtC As Date
Dim objD As Object
Dim varE As Variant

only varE can be assigned a Null value. Any of the following
statements:

strA = Null '**NO
lngB = Null '**NO
dtC = Null '**NO
objD = Null '**NO

will raise an error if you try to execute it.

 

[Bill]

Your suspicion is exactly correct. No question that I'd failed to
make the necessary distinction between database field names and
controls versus VBA variables. That subtlety, if I can call it that,
never clearly surfaced during those times that I "bumped my head"
in the midst of debugging code. It was out of those experiences that
I was led to conclude that Null was strictly for variants regardless of
context, and what really led to the beginning of this thread.

You have done me a great service in clearing up this all important
point and I thank you.

Bill

 

[RipperT]

Is it not true that a check box, cboBox or toggle button can have a Null
value it it's triple state property is set to 'yes'?

Rip

 

[Dirk Goldgar]

Is it not true that a check box, cboBox or toggle button can have a
Null value it it's triple state property is set to 'yes'?

They can if they aren't bound to a boolean field. The problem here lies
in the nature of boolean fields in Jet -- they can't hold Null values.
If you leave those controls unbound, or bind them to number fields
instead of boolean fields, then they can have Null values.