What is the code for allowing zero lengths in strings for text box

[J]

Say I have a table called Table1 with fields A, B, and C. I create a combo
box (combo1) on a report called Report1. The combo fills in itself and
populates text boxes (box1, box2) corresponding to certain columns. However,
some fields in the table are blank and I get a zero length string error.

The following code is typed into the after update event in my combo box:

Me.box1 = Me.combo1.Column(2)
Me.box2 = Me.combo1.Column(3)

I can populate the text boxes only when there is no blanks in the fields
within the table. What's the code so that I may have zero length strings in
my text boxes? Where am I supposed to type this code in VBA?

 

[Allen Browne]

To assign the value from the column to the field only if there is some text
in the column, use this approach:

With Me.Combo1
If .Column(2) <> vbNullString Then
Me.box1 = .Column(2)
End If
'repeat for .Column(3)
End With

Note that there is a difference between a Null and a zero-length string
(ZLS.) The Column() property could be a ZLS which you don't want in your
table field. If the column of the combo doesn't contain any characters, the
code doesn't try to assign anything to the box, so the field is unchanged
(e.g. still Null.)

It is possible to open the table in design view and set the Allow Zero
Length property to Yes for the field, but I strongly recommend against doing
that. It's way to confusing for you (and your users) to distinguish between
the ZLS and Null values when there is no visible difference between them.
More info:
http://allenbrowne.com/bug-09.html

 

[David W. Fenton]

It is possible to open the table in design view and set the Allow
Zero Length property to Yes for the field, but I strongly
recommend against doing that. It's way to confusing for you (and
your users) to distinguish between the ZLS and Null values when
there is no visible difference between them.

Also, there's no way to actually *enter* a ZLS in an Access control,
except via code or the Immediate Window (even when it's bound to a
field that allows ZLS).

 

[Sky]

Also, there's no way to actually *enter* a ZLS in an Access control,
except via code or the Immediate Window (even when it's bound to a
field that allows ZLS).

For as long as I can remember (back to Access 95), you could enter ""
and get a zero-length string.

- Steve

 

[Allen Browne]

My experience is that typing "" enters a ZLS.

I have even used that as the default value on the rare occasions where I
wanted to use a ZLS field.

 

[David W. Fenton]

For as long as I can remember (back to Access 95), you could enter
"" and get a zero-length string.

Well, that one I didn't know about.

It doesn't change my mind about how ill-advised allowing ZLS is. The
only place I'd ever use it is for imports from sources where I can't
control, but I would likely process all the data to get rid of the
ZLS's before using it in any actual app.

 

[Sky]

Well, that one I didn't know about.

It doesn't change my mind about how ill-advised allowing ZLS is. The
only place I'd ever use it is for imports from sources where I can't
control, but I would likely process all the data to get rid of the
ZLS's before using it in any actual app.

I agree that I would never use ZLS. Even with external data, I convert
ZLS to Null during the import.

I was just correcting the invalid statement.

Steve

 

[David W. Fenton]

I agree that I would never use ZLS. Even with external data, I
convert ZLS to Null during the import.

I was just correcting the invalid statement.

Thanks for that -- I learned something from making that wrong
statement.

 

[Stuart McCall]

I learned something from making that wrong statement.

This happens to me all the time, for which I'm always grateful.

I call it 'learning by provocation' <g>

 

[Sky]

Thanks for that -- I learned something from making that wrong
statement.

A very gracious response.