Comma In VBA Value List

  • Thread starter Thread starter DS
  • Start date Start date
D

DS

I can't get this comma in this listbox without starting a new row. Any help
Appreciated.
Thanks
DS

Forms!frmFXDiscountSub!ListSub.RowSourceType = "Value List"
Forms!frmFXDiscountSub!ListSub.RowSource = "1;Discount Main Item
Only;4;Discount Both Main and Modifier;6;Discount Main Item, Modifier and
Sub-Modifier"

Forms!frmFXDiscountSub!ListSub.ColumnCount = 2
Forms!frmFXDiscountSub!ListSub.ColumnWidths = ".2 in;3 in"

I tried quotes ","
I tried more quotes """",""""

Still can't figure it out.
 
I think this is what you are looking for:

1;"Discount Main Item Only";4;"Discount Both Main and Modifier";6;"Discount
Main Item, Modifier and Sub-Modifier"

If you want the numbers to appear on the same line as the text, you will
need to move it inside the quotations, and remove the semi-colon or it will
be seen.
 
Thank you for helping. Sorry to say it didn't work.
It's all in red. It should end up in two columns like this

1 Discount Main Item Only
4 Discount Both Main and Modifier
6 Discount Main Item, Modifier and Sub-Modifier

The Numbers in One Column....The Text in another.

Thanks
DS
 
I don't understand the red part. If you don't have 2 columns, it's because
you do not have your column count property set to 2, and/or your column
widths property set properly.
 
I have the columns set to 2 and the widths set to .2 in and 2 in

The code is in red because the syntax isn't correct...

Me.List5.RowSource = ;1;"Discount Main Item Only";4;"Discount Both Main Item
and Modifier";6;"Discount Main Item, Modifier and Sub-Modifier"

I need it to end up in two columns....
column 1 is numerical and column 2 is the text as such.

1 Discount Main Item Only
2 Discount Both Main Item and Modifier
3 Discount Main Item, Modifier and Sub-Modifier

Once again, thank you for helping..
DS
 
Me.List5.RowSource = "1;Discount Main Item Only;4;Discount Both Main Item
and Modifier;6;Discount Main Item, Modifier and Sub-Modifier"

In other words, no interior quotes: just one at the start and end (and no
semi-colon in front)
 
Simple answer:

You can NOT place a comma in your value list, since ms-access uses both ";"
and "," for the delimiter. You MUST remove the "," from the text, or place
the values in a table, and base the listbox on that...you have no choice in
the matter....
 
Tried that also....this works

Dim INFO As String
INFO = "1;""Discount Main Item Only"";4;""Discount Both Main and
Modifier"";6;""Discount Main Item, Modifier and Sub-Modifier"""

Me.List5.RowSourceType = "Value List"
Me.List5.RowSource = INFO

Thanks
DS
 
I never fail to find the hard ones :)
Thank You Albert.

See my post to Douglas what I ended up doing.

Thanks
DS
 
Try it in the property sheet. If it works for you, try wrapping the entire
thing with 4 double quotes.
 
Yes I did Doug, for whatever reason it's breaking it at the last one

1 Discount Main Item Only
4 Discount Both Main Item and Modifier
6 Discount Main Item,
Modifier and Sub-Modifier"

Thanks
DS
 
Son of a gun, you're right! I must have miscopied into my test.

Glad you got it working.
 
"DS" wrote in message
news:%[email protected]...
> Yes I did Doug, for whatever reason it's breaking it at the last one
>
> 1 Discount Main Item Only
> 4 Discount Both Main Item and Modifier
> 6 Discount Main Item,
> Modifier and Sub-Modifier"
Click to expand...

The answer is simple, although I admit I was stumped for quite a while. A list box handles values in one of two formats.

1- value1; value2; value 3;
-or-
2- "value1"; "value2"; "value3" <----notice the quotes around each value.

In order to fix your problem you need to somehow place quotes around each piece of information like so:

"6"; "Discount Main Time, Modifier and Sub-Modifier"

The quotes tell the computer to view each section as an entire phrase. The easiest way to do this is to use the apostraphe enclosed in a set of quotes - "'" or four quotes together - """".

Depending on how you are obtaining your information you can either use the REPLACE commnad or perhaps the & (concatenate) command.

Hope that helps.
 
Back
Top