Access 2003 - How to show ValueList listbox data containing commas?

[Alan Lane]

Hello world:

I have a ListBox that I fill as a ValueList from a SQL query. The SQL looks
like:

SELECT Format(COST,"$#,000") As ItemCost FROM tblPricing ...

I put semicolons between each entry before I load it into the ListBox.
However, if my entry has a comma in it ($2,500 for example) Access adds
another column, thereby messing up the ListBox display. It shows as $2 and
then 500.

Why is Access using a comma as an list separator, when it is only supposed
to use semicolons? Is there any way to escape the comma, and have it be
accepted as just another character in the ListBox display?

Thanks in advance for any help offered.

Alan

[rkc]

Alan Lane wrote:

Hello world:

I have a ListBox that I fill as a ValueList from a SQL query. The SQL looks
like:

SELECT Format(COST,"$#,000") As ItemCost FROM tblPricing ...

I put semicolons between each entry before I load it into the ListBox.
However, if my entry has a comma in it ($2,500 for example) Access adds
another column, thereby messing up the ListBox display. It shows as $2 and
then 500.

Why is Access using a comma as an list separator, when it is only supposed
to use semicolons? Is there any way to escape the comma, and have it be
accepted as just another character in the ListBox display?

Trying enclosing each item in quotes.

Function escapeListItem(ByVal s As String) As String
escapeListItem = Chr$(34) & s & Chr$(34)
End Function
Access.Version > 97 = .AddItem (escapeListItem(rs!ItemCost))

[Tim Marshall]

Alan Lane wrote:

I put semicolons between each entry before I load it into the ListBox.
However, if my entry has a comma in it ($2,500 for example) Access adds
another column, thereby messing up the ListBox display. It shows as $2 and
then 500.

Commas and semi-colons are used. In cases like this, I delimit my
values with double quotes. So, for example (air code), using DAO::

Sub sPopulateList

dim dbs as DAO.database
dim rst as DAO.recordset
dim strS as string
dim strList as string 'value list

set dbs = access.currentdb

'I'm not using any where clause below.
'Note the double quotes in the format function

strs = "SELECT Format(COST,""$#,000"") As ItemCost FROM tblPricing"

set rst = dbs.openrecordset(strs, dbopensnapshot)

with rst

if .eof then

'slap in some message for no records found

else

.movefirst

strlist = ""

do while .eof = false

if strList <> "" then strlist = strList & ";"

'Chr(34) is the ascii code for "

strList = chr(34) & .fields!ItemCost & Chr(34)

.movenext

loop

end if

end with

'populate the value list

me.lstMyListBox.Rowsource = strList

Exit_Proc:

rst.close

set rst = nothing

dbs.close

set dbs = nothing

End Sub
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me

[Alan Lane]

Hello Tim and rkc:

Thanks for the help from both of you. The double-quotes idea didn't work
for me, unfortunately. I took another part of both your suggestions,
though, and used "Chr(34)" before and after the ItemCost, when entering it
into the ListBox's ValueList. That worked like a charm!

Alan

"Tim Marshall" <TI****@PurplePandaChasers.Moertherium> wrote in message
news:d9**********@coranto.ucs.mun.ca...

Alan Lane wrote:

I put semicolons between each entry before I load it into the ListBox.
However, if my entry has a comma in it ($2,500 for example) Access adds
another column, thereby messing up the ListBox display. It shows as $2
and then 500.

Commas and semi-colons are used. In cases like this, I delimit my values
with double quotes. So, for example (air code), using DAO::

Sub sPopulateList

dim dbs as DAO.database
dim rst as DAO.recordset
dim strS as string
dim strList as string 'value list

set dbs = access.currentdb

'I'm not using any where clause below.
'Note the double quotes in the format function

strs = "SELECT Format(COST,""$#,000"") As ItemCost FROM tblPricing"

set rst = dbs.openrecordset(strs, dbopensnapshot)

with rst

if .eof then

'slap in some message for no records found

else

.movefirst

strlist = ""

do while .eof = false

if strList <> "" then strlist = strList & ";"

'Chr(34) is the ascii code for "

strList = chr(34) & .fields!ItemCost & Chr(34)

.movenext

loop

end if

end with

'populate the value list

me.lstMyListBox.Rowsource = strList

Exit_Proc:

rst.close

set rst = nothing

dbs.close

set dbs = nothing

End Sub
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me

[Tim Marshall]

Alan Lane wrote:

Hello Tim and rkc:

Thanks for the help from both of you. The double-quotes idea didn't work
for me, unfortunately. I took another part of both your suggestions,
though, and used "Chr(34)" before and after the ItemCost, when entering it
into the ListBox's ValueList. That worked like a charm!

You actually can put double quotes in using _double_ double quotes (boy,
does that sound confusing). It can be difficult to keep track of and
when I can't be bothered, I find the use of chr(34) easier.

another useful delimiter (though not for list/combo box rowsources) is
chr(35) which is #.

--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me