Search code examples
vbams-accesslistboxms-access-forms

Problem with display currency in Listbox on MS-Access

Any time the listbox in MS-Access receives data, it will automatically consider commas, semi-colons or other types of delimiters and separate the values into columns within the listbox.

Therefore, when I format the values to currency the comma it finds in $2,000 (for example) gets delimited to $2 (in column 1) and 000 (in column 2).

I did set column count to 4, so I see why it is doing that but there must be a different way...

I set the column count to 4 because I have 4 values that I want displayed in the list box.

List40.ColumnCount = 4
List40.AddItem (strProduct & ";" & mQty & ";" & format(uPricing,"Currency") & ";" & format(tPricing,"Currency"))

In summary I expect my list to look like this

+---------+----------+-----------+-----------+
| PRODUCT | QUANTITY | UNITPRICE | TOTALCOST |
+---------+----------+-----------+-----------+
| abc     |        5 | $500.00   | $2,500.00 |
+---------+----------+-----------+-----------+

Instead I see: 

+---------+----------+-----------+-----------+
| PRODUCT | QUANTITY | UNITPRICE | TOTALCOST |
+---------+----------+-----------+-----------+
| abc     |        5 | $500.00   | $2        |
+---------+----------+-----------+-----------+
Solution

  • Enclose the value between double quotes (ASCII code 34):

    .....& ";" & Chr(34) & format(tPricing,"Currency") & Chr(34))