What is a zero-length field?

[Tammy]

Hi,

I have read many posts, and have tried looking up what exactly a zero-length
field is, but am just being a little dumb. It seems all the posts regarding a
zero-length problem are from people who already "get it."

I keep seeing that a zero-length field = " "

Does that mean a zero-length field is a space? I've read Allen Browne really
well-written explanation on the difference between a zls and a null, but am
just confused on how a zls is displayed.

I realize I wouldn't be able to "see" a space - is that built into the
design of the table?

I'm really sorry if this is just kind of a dumb question, but it's something
i've wondered about for a while and am hoping to get an explanation in a
non-techy way. I'm not a VB person - just work with the design views in a
database.

Thanks for any clarification!

 

[Lance]

Always remember that what you can see is not the same as what a computer can
see.

a field = " " has a length of 1
a field = "" has a length of 0, and is a zero length field

Only certain data types ( like variant ) can be null. If you want to test
this try something like:

Dim X as integer

X = null

This will choke.

Then trim

Dim X as variant

X = null

This will work.


I'm not sure that this relates directly to the null messages in access
queries though.

 

[Jerry Whittle]

That should be "" and not " ". A space would not be zero length. Zero lenght
means that there is nothing in it.

That brings us to a Null value. Null means that you don't know.

A ZLS is displayed as nothing. If fact looking at an "empty" field, it could
be a Null, ZLS, some spaces, and maybe, just maybe, some non-printable ASCII
characters like a tab or paragraph return.

When confonted with an empty looking field that isn't working as expected, I
check the data with queries looking for things such as Like " *" ; Null; and
"" . I don't worry about non-printable ASCII characters until all else fails.

Of course if you check the design of the text field to see Allow Zero Lenght
settings. If is set to No, you can discount that the empty-looking field is a
ZLS.

Actually you can "see" a space if you try. If you click on they right-most
part of the text box, the cursor should stop at the end of the string. You
could then use the left arrow key to see if the curson moves or sweep across
it with the mouse to see if something gets highlighted. Also if your eyes are
really good, you might be able to see that the cursor is just a little to the
right of normal if it was a Null or ZLS.

 

[Jeff Boyce]

Are you asking about a "zero-length string"? That would be a pair of
double-quotes WITHOUT any spaces (a space is a character, too).


Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Dirk Goldgar]

In

Hi,

I have read many posts, and have tried looking up what exactly a
zero-length field is, but am just being a little dumb. It seems all
the posts regarding a zero-length problem are from people who already
"get it."

I keep seeing that a zero-length field = " "

Does that mean a zero-length field is a space? I've read Allen Browne
really well-written explanation on the difference between a zls and a
null, but am just confused on how a zls is displayed.

I realize I wouldn't be able to "see" a space - is that built into the
design of the table?

I'm really sorry if this is just kind of a dumb question, but it's
something i've wondered about for a while and am hoping to get an
explanation in a non-techy way. I'm not a VB person - just work with
the design views in a database.

Thanks for any clarification!

No, a zero-length field is *not* a space, and it wouldn't normally be
represented as " " (quote-space-quote), but rather as "" (quote-quote).
However, people sometimes space out the quotes in a newsgroup post to
make it easier to see the number and placement of the quotes, and that
may be misleading.

In a text box on a form or in a cell of a datasheet, you won't be able
to see the difference between a Null and a zero-length string. The ZLS
won't be displayed as ""; the box will just be blank. I'd prefer that
there be some easy user-interface tag or widget that would let you just
look and see. As it is, when I need to see whether a field is Null or a
ZLS, I apply a filter for (Is Null) or (= "").

 

[Tammy]

Thanks so much for answering my post, Jerry and Lance (although, I think you
were referencing VBA, Lance, which I'm not familiar with, but truly
appreciate your explanation).

This does help a lot!