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?

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

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


"J" <(E-Mail Removed)> wrote in message
news:B23DE21B-2586-4C13-9168-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in
news:uJpDq#(E-Mail Removed):

> 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).

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

On 1/30/2010 12:03 PM, David W. Fenton wrote:
>
> 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

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.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


"Sky" <(E-Mail Removed)> wrote in message
news:eI47$(E-Mail Removed)...
> On 1/30/2010 12:03 PM, David W. Fenton wrote:
>>
>> 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
>

Sky <(E-Mail Removed)> wrote in
news:eI47$(E-Mail Removed):

> On 1/30/2010 12:03 PM, David W. Fenton wrote:
>>
>> 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.

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.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

On 1/30/2010 8:41 PM, David W. Fenton wrote:
> Sky<(E-Mail Removed)> wrote in
> news:eI47$(E-Mail Removed):
>
>> On 1/30/2010 12:03 PM, David W. Fenton wrote:
>>>
>>> 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.
>
> 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

Sky <(E-Mail Removed)> wrote in
news:(E-Mail Removed):

> On 1/30/2010 8:41 PM, David W. Fenton wrote:
>> Sky<(E-Mail Removed)> wrote in
>> news:eI47$(E-Mail Removed):
>>
>>> On 1/30/2010 12:03 PM, David W. Fenton wrote:
>>>>
>>>> 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.
>>
>> 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.

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

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

> 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>

On 2/1/2010 3:35 PM, David W. Fenton wrote:
> Sky<(E-Mail Removed)> wrote in
> news:(E-Mail Removed):
>
>> On 1/30/2010 8:41 PM, David W. Fenton wrote:
>>> Sky<(E-Mail Removed)> wrote in
>>> news:eI47$(E-Mail Removed):
>>>
>>>> On 1/30/2010 12:03 PM, David W. Fenton wrote:
>>>>>
>>>>> 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.
>>>
>>> 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.
>
> Thanks for that -- I learned something from making that wrong
> statement.
>

A very gracious response.