Listbox question

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

troon - 31 May 2007 22:32 GMT

I have a listbox that contains some numeric values like "1","2","3" etc...
How can I change the what these values display on a form in the listbox?
What I'd like to do is this: if the value = "1" display "Black" If the value
= "2" display "Red" if the value = "3" display "Blue"? How can I do that in
a list box? I tried doing it with an IIF but that only works for 2 values.
Access 97
Thanks

Reply to this Message

Mr. B - 31 May 2007 22:45 GMT

> I have a listbox that contains some numeric values like "1","2","3" etc...
> How can I change the what these values display on a form in the listbox?
[quoted text clipped - 3 lines]
> Access 97
> Thanks

troon,

Make sure that the "Row Source Type" property for the listbox is set
to "Value List"

Then in the "Row Source" property, enter the values you want like
this:

1;"Black";2;"Red";3;"Blue"

You can continue this type of entry for as many different entries as
your need.

Next, set the "Column Count" property to 2 and then enter the
following in the "Column Widths" property"

0;2"

That is a zero and a semi colon and then another value that would
represent the total width of your list box.

HTH

Mr. B

Reply to this Message

troon - 31 May 2007 22:58 GMT

Thanks Mr. B The Row Source contains my Select statement. Can I put
1;"Black";2;"Red";3;"Blue" in this select statment?

Thanks

> > I have a listbox that contains some numeric values like "1","2","3" etc...
> > How can I change the what these values display on a form in the listbox?
[quoted text clipped - 28 lines]
>
> Mr. B

Reply to this Message

Ofer Cohen - 31 May 2007 22:57 GMT

Create a table with two fields
Number Color
1 Black
2 Red
3 Blue

Etc

Create the list box in the form to be based on this table
***************
About the IIf it can return more then two values using nested IIf (IIf
within IIf)
IIf([Color]=1, "Black",IIf([Color] = 2 , "Red","Blue"))

Or you can use the Switch function

Switch( [Color]=1,"Black" ,[Color]=2,"Red",[Color]=3,"Blue")

Signature

Good Luck
BS"D

Reply to this Message

troon - 31 May 2007 23:05 GMT

Thanks, that was my first thought was to use a lookup table however this data
is linked from a view in SQL server so I am not able to create a relationship
from a table in Access with a view from SQL server.

Thanks

> Create a table with two fields
> Number Color
[quoted text clipped - 21 lines]
> > Access 97
> > Thanks

Reply to this Message

troon - 31 May 2007 23:32 GMT

Thanks Very much Ofer, i think the IIF syntax was what I needed. Thanks very
much for the help

Regards

> Create a table with two fields
> Number Color
[quoted text clipped - 21 lines]
> > Access 97
> > Thanks

Reply to this Message

Mr. B - 31 May 2007 23:46 GMT

> Thanks Very much Ofer, i think the IIF syntax was what I needed. Thanks very
> much for the help
[quoted text clipped - 31 lines]
>
> - Show quoted text -

troon,

You can also use a query (select statement) by including the numeric
value field (which you are evidently already returning) and the as the
second field in your query paste the following statement:

ValToView: Choose([tblTestNumberOnly]!
[Field1],"Black","Red","Blue","Green")

Just reference you numeric field where I have [tblTestNumberOnly]!
[Field1]

This way you can simply add the text value you want returned for each
value returned by the numeric field. The text values just have to be
in the correct order.

HTH

Mr. B

Reply to this Message