List Vs Combo and Null values

[Debbiedo]

Why when I have this syntax in the Row Source for a List Box, the null
values display the word "CAN" but when I convert it to a Combo Box (to
eliminate the annoying black background) the word "CAN" does not
appear? What would be the work around for this? I want to stay away
from macros because this report is shared and macros locked out by
many of its users.

"";"CAN";"X";"CANNOT"


Thanks

Deb

 

[Jeff Boyce]

Deb

Using a value list is one way to "fill" a list and/or a combobox.

Have you tried putting the acceptable values into a table and basing your
'boxes on a query against that table?

NOTE: if you only have two choices, consider using the Option Group/radio
buttons.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Brent Spaulding \(datAdrenaline\)]

Is the control BOUND to a Field of TEXT datatype? ... if so, your field
should be defined to allow Zero Length Strings and possibly set the default
value to "" (a zero length string) simply because a ListBox and ComboBox
will only return a Null if NO ITEM is selected, if an item is selected the
control will return a String ...even if the bound column is numeric, it is
still a string.

 

[Debbiedo]

Deb

Using a value list is one way to "fill" a list and/or a combobox.

Have you tried putting the acceptable values into a table and basing your
'boxes on a query against that table?

NOTE:  if you only have two choices, consider using the Option Group/radio
buttons.

Regards

Jeff Boyce
Microsoft Office/Access MVP






- Show quoted text -

I do not have write access to the table. It is a linked table that is
created nightly and used for several applications besides my report.
Changing the data is not practicle. It is better that I create a form
that works with what I have currently. I am designing the form to
mimic an existing form that has been through an approval process.
Changing the format would be difficult. I need to be able to display
the word "Can" when the field is Null and "Cannot" when the field has
an "X" so I believe Option Group or Radio Button would not work. Any
other ideas?

Thanks Deb

 

[Debbiedo]

Is the control BOUND to a Field of TEXT datatype? ... if so, your field
should be defined to allow Zero Length Strings and possibly set the default
value to "" (a zero length string) simply because a ListBox and ComboBox
will only return a Null if NO ITEM is selected, if an item is selected the
control will return a String ...even if the bound column is numeric, it is
still a string.

--
Brent Spaulding | datAdrenaline | Access MVP






- Show quoted text -

The properties are set to Allow Zero Length and default is set to "",
but the results are still displayed only if the value in the field
equals "X". The word "Can" does not display when the value in the
field is Null or "".

Any other ideas? Can I do this using vb, if so, how? I may have to
just pass the word not to disable macros.

Thanks

Deb

 

[Debbiedo]

Why when I have this syntax in the Row Source for a List Box, the null
values display the word "CAN" but when I convert it to a Combo Box (to
eliminate the annoying black background) the word "CAN" does not
appear? What would be the work around for this? I want to stay away
from macros because this report is shared and macros locked out by
many of its users.

"";"CAN";"X";"CANNOT"

Thanks

Deb

I got it!

I changed the combo box to a text box and typed the following into the
Control Source property box.

=IIf([ALLOW] Is Not Null,"Cannot","Can")

As usual, trying to make things harder than they need to be. Thanks to
all who pointed me in the right direction.

 

[Brent Spaulding \(datAdrenaline\)]

Excellent!! ... I am glad you got it sorted out. But I leave you with
caution in that your expression will not catch a ZLS, so I propose this:

=IIf(Len([ALLOW] & "") = 0, "Can", "Cannot")

--
Brent Spaulding | datAdrenaline | Access MVP

Why when I have this syntax in the Row Source for a List Box, the null
values display the word "CAN" but when I convert it to a Combo Box (to
eliminate the annoying black background) the word "CAN" does not
appear? What would be the work around for this? I want to stay away
from macros because this report is shared and macros locked out by
many of its users.

"";"CAN";"X";"CANNOT"

Thanks

Deb

I got it!

I changed the combo box to a text box and typed the following into the
Control Source property box.

=IIf([ALLOW] Is Not Null,"Cannot","Can")

As usual, trying to make things harder than they need to be. Thanks to
all who pointed me in the right direction.