By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,191 Members | 2,190 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,191 IT Pros & Developers. It's quick & easy.

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

P: n/a
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
Nov 13 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
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))



Nov 13 '05 #2

P: n/a
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
Nov 13 '05 #3

P: n/a
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

Nov 13 '05 #4

P: n/a
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
Nov 13 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.