Trying to populate a listbox using additem (Access 2007 VBA). Whenever an
item to be added contains a comma, it truncates it at that spot. Could it be
that it mistakes the comma for a semicolon and expects it to belong to a
different column?

Try putting quotes around it.

Instead of

MyListbox.AddItem "1;A, B, C"

try


MyListbox.AddItem "1;""A, B, C"""



--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"FR" <(E-Mail Removed)> wrote in message
news:AD2B674E-3DB1-49A1-B889-(E-Mail Removed)...
> Trying to populate a listbox using additem (Access 2007 VBA). Whenever an
> item to be added contains a comma, it truncates it at that spot. Could it
> be
> that it mistakes the comma for a semicolon and expects it to belong to a
> different column?

"Douglas J. Steele" wrote:

> Try putting quotes around it.
>
> Instead of
>
> MyListbox.AddItem "1;A, B, C"
>
> try
>
>
> MyListbox.AddItem "1;""A, B, C"""
>
>
>
> --
> Doug Steele, Microsoft Access MVP
> http://I.Am/DougSteele
> (no private e-mails, please)
>
>
> "FR" <(E-Mail Removed)> wrote in message
> news:AD2B674E-3DB1-49A1-B889-(E-Mail Removed)...
> > Trying to populate a listbox using additem (Access 2007 VBA). Whenever an
> > item to be added contains a comma, it truncates it at that spot. Could it
> > be
> > that it mistakes the comma for a semicolon and expects it to belong to a
> > different column?
>
>
Thank you very much. Unfortunately the string comes from a recordset as
shown below

Do While Not rs.EOF
lbSelMem.AddItem rs.Fields(0).Value & ";" & rs.Fields(21).Value
rs.MoveNext
Loop

Fields(21) contains the string with the comma. I tested it by adding extra
columns to the listbox and sure enough text beyond the commas was put in
subsequent columns. I think this indicates that Access treats commas as if
they were semicolons. If so, is this a bug? And if so what is the procedure
to let Microsoft know?

Thanks again for your help.

I am sure that Douglas is right. It is just how you use them. In your case
try inserting the double quote like this with the chr(34).

lbSelMem.AddItem chr(34) & rs.Fields(0).Value & chr(34) & ";" & chr(34) &
rs.Fields(21).Value & chr(34)

I assume that using the semi colon that you want two columns.

--
Regards,

OssieMac


"FR" wrote:

>
>
> "Douglas J. Steele" wrote:
>
> > Try putting quotes around it.
> >
> > Instead of
> >
> > MyListbox.AddItem "1;A, B, C"
> >
> > try
> >
> >
> > MyListbox.AddItem "1;""A, B, C"""
> >
> >
> >
> > --
> > Doug Steele, Microsoft Access MVP
> > http://I.Am/DougSteele
> > (no private e-mails, please)
> >
> >
> > "FR" <(E-Mail Removed)> wrote in message
> > news:AD2B674E-3DB1-49A1-B889-(E-Mail Removed)...
> > > Trying to populate a listbox using additem (Access 2007 VBA). Whenever an
> > > item to be added contains a comma, it truncates it at that spot. Could it
> > > be
> > > that it mistakes the comma for a semicolon and expects it to belong to a
> > > different column?
> >
> >
> Thank you very much. Unfortunately the string comes from a recordset as
> shown below
>
> Do While Not rs.EOF
> lbSelMem.AddItem rs.Fields(0).Value & ";" & rs.Fields(21).Value
> rs.MoveNext
> Loop
>
> Fields(21) contains the string with the comma. I tested it by adding extra
> columns to the listbox and sure enough text beyond the commas was put in
> subsequent columns. I think this indicates that Access treats commas as if
> they were semicolons. If so, is this a bug? And if so what is the procedure
> to let Microsoft know?
>
> Thanks again for your help.
>

"OssieMac" wrote:

> I am sure that Douglas is right. It is just how you use them. In your case
> try inserting the double quote like this with the chr(34).
>
> lbSelMem.AddItem chr(34) & rs.Fields(0).Value & chr(34) & ";" & chr(34) &
> rs.Fields(21).Value & chr(34)
>
> I assume that using the semi colon that you want two columns.

It worked!! You were absolutely right and I am most grateful
>
> --
> Regards,
>
> OssieMac
>
>
> "FR" wrote:
>
> >
> >
> > "Douglas J. Steele" wrote:
> >
> > > Try putting quotes around it.
> > >
> > > Instead of
> > >
> > > MyListbox.AddItem "1;A, B, C"
> > >
> > > try
> > >
> > >
> > > MyListbox.AddItem "1;""A, B, C"""
> > >
> > >
> > >
> > > --
> > > Doug Steele, Microsoft Access MVP
> > > http://I.Am/DougSteele
> > > (no private e-mails, please)
> > >
> > >
> > > "FR" <(E-Mail Removed)> wrote in message
> > > news:AD2B674E-3DB1-49A1-B889-(E-Mail Removed)...
> > > > Trying to populate a listbox using additem (Access 2007 VBA). Whenever an
> > > > item to be added contains a comma, it truncates it at that spot. Could it
> > > > be
> > > > that it mistakes the comma for a semicolon and expects it to belong to a
> > > > different column?
> > >
> > >
> > Thank you very much. Unfortunately the string comes from a recordset as
> > shown below
> >
> > Do While Not rs.EOF
> > lbSelMem.AddItem rs.Fields(0).Value & ";" & rs.Fields(21).Value
> > rs.MoveNext
> > Loop
> >
> > Fields(21) contains the string with the comma. I tested it by adding extra
> > columns to the listbox and sure enough text beyond the commas was put in
> > subsequent columns. I think this indicates that Access treats commas as if
> > they were semicolons. If so, is this a bug? And if so what is the procedure
> > to let Microsoft know?
> >
> > Thanks again for your help.
> >