Formatted strings in a Listbox

[Solo712]

I am using a listbox which is populated at runtime with stats. I have a really strange problem with a string formatted for currency, like so:

.AddItem "Budget for " & Str(Year(Date)) & " :" & stBudgetStr

the stBudgetStr being previously assigned value in this manner :

stBudgetStr = Format(stBudget, "##,###,##0")

the stBudget variable is $688,190. What the list box displays is

Budget for 2012 :688

i.e. the reading is dropped after the first comma. I have played around with this problem and indeed if the budget figure is over a million, the listbox stops displaying when it encounters the first comma, ie. at the millions. Nothing helps. If I add another string after the figure, it is dropped with the rest - always at the first comma. In contrast, if I concatenate the string with Str(stBudget) the figure displays in full.

I am using Access 2007 SP3. Is this a known issue ? And is there a workaround ?

Best,
Jiri

 

[Solo712]

I am using a listbox which is populated at runtime with stats. I have a really strange problem with a string formatted for currency, like so:

.AddItem "Budget for " & Str(Year(Date)) & " :" & stBudgetStr

the stBudgetStr being previously assigned value in this manner :

stBudgetStr = Format(stBudget, "##,###,##0")

the stBudget variable is $688,190. What the list box displays is
i.e. the reading is dropped after the first comma. I have played around with this problem and indeed if the budget figure is over a million, the listbox stops displaying when it encounters the first comma, ie. at the millions. Nothing helps. If I add another string after the figure, it is dropped with the rest - always at the first comma. In contrast, if I concatenate the string with Str(stBudget) the figure displays in full.

I am using Access 2007 SP3. Is this a known issue ? And is there a workaround ?

Best,
Jiri

Hello, can someone look at this ?

Best,
Jiri

 

[joemach]

Have you tried using CCUR function to convert the data to currency? Not using 2007 so not sure if something changed since 2003.

Hope you get the answer you need. Sorry I couldn't help more.

 

[Solo712]

Have you tried using CCUR function to convert the data to currency? Not using 2007 so not sure if something changed since 2003.

Hope you get the answer you need. Sorry I couldn't help more.

Thanks for answering, Joe. The variable converted into string is already typed as "Currency". The string in the example above is well-formed, besides as I said, the Str function displays the value correctly so the issue does not appear to be conversion but text presentation in the listbox. I have tested it further and the control truncates also non-numerical strings that contain commas. Definitely looks like a bug.

Best,
Jiri

 

[nanscombe]

It may be because you are using the listbox as a "Value list". Since each column is separated by a comma it is probably using the commas in the currency as a column breaks.

Budget for 2012 : 680 (new column) 190

Try this.

.AddItem Chr(34) & "Budget for " & Str(Year(Date)) & " :" & stBudgetStr & Chr(34)

Each line will now be enclosed in "" so the whole line will be interpreted as a string.

"Budget for 2012 : 680,190"

 

[Solo712]

It may be because you are using the listbox as a "Value list". Since each column is separated by a comma it is probably using the commas in the currency as a column breaks.

Budget for 2012 : 680 (new column) 190

Try this.

.AddItem Chr(34) & "Budget for " & Str(Year(Date)) & " :" & stBudgetStr & Chr(34)

Each line will now be enclosed in "" so the whole line will be interpreted as a string.

"Budget for 2012 : 680,190"

Yup, that works ! Funny, I tried previously

.AddItem  "Budget for " & Str(Year(Date)) & " : '" & stBudgetStr & "'"

but that did not do the trick, so I was losing hope there was a solution to this given also that the list box is a single column. There is no reason on earth to interpret

  .AddItem "Value with an embedded comma "

as

 .AddItem "Value1", "Value2"

This should be fixed (if it wasn't fixed in Access 2010). At any rate, you solved this for me, nanscombe, and I thank you. The display looks much prettier formatted.

Best,
Jiri

 

[nanscombe]

There is nothing to fix. It works perfectly when applied correctly.

You tried it with a ' at one end, and it failed. If you had put a ' at both ends it would probably have worked as well.

You were trying to pass in Budget for 2012: 680,190 not "Budget for 2012: 680,190" or 'Budget for 2012: 680,190'.

Access basically threw away the " it was using to make up the string, it needed the extra " or ' to define what was passed as a string.

Normally you can pass multiple columns in one line.

"'column 1', 2, 'column 3'"

Or

"'column 1'; 2; 'column 3'"

That would be interpreted:
First column a string "column 1"
Second column a number 2
Third column a string "column 3"

 

[Solo712]

There is nothing to fix. It works perfectly when applied correctly.

You tried it with a ' at one end, and it failed. If you had put a ' at both ends it would probably have worked as well.

You were trying to pass in Budget for 2012: 680,190 not "Budget for 2012: 680,190" or 'Budget for 2012: 680,190'.

Access basically threw away the " it was using to make up the string, it needed the extra " or ' to define what was passed as a string.

Normally you can pass multiple columns in one line.

"'column 1', 2, 'column 3'"

Or

"'column 1'; 2; 'column 3'"

That would be interpreted:
First column a string "column 1"
Second column a number 2
Third column a string "column 3"

Nigel, you solved my problem and I thanked you for it. I m glad there is a workaround for this problem but I won't let you snow me on this. What you are advocating here is coding anarchy. There is no bloody way the interpreter should be confused about how many arguments have been passed to it or for that matter how many columns are being instantiated. The string that I passed to the AddItem method originally was formed compliant to the VBA syntax. It addressed a single-column listbox, as I explained. The interpreter truncated the argument. That is all there is to it.

Best,
Jiri

 

[nanscombe]

Take it up with Microsoft ....

AddItem Method

For multiple-column lists, use semicolons to delimit the strings for each column (for example, "1010;red;large" for a three-column list). If the Item argument contains fewer strings than columns in the control, items will be added starting with the left-most column. If the Item argument contains more strings than columns in the control, the extra strings are ignored.

It so happens that commas work as delimiters as well as semicolons. The extra bits of your numbers were being ignored.

Using " or ' around strings is pretty standard.

 

[Solo712]

Take it up with Microsoft ....

I will.

It so happens that commas work as delimiters as well as semicolons.

Can you give me another example of a computer language where by design rule semi-colons have the same syntactical function as commas ? I can't think of any, and I think I know why.

Now I have done a little research into this and found out that the AddItem method was added in Access 2002. The designers of that function had every opportinity to create syntax consistent with other VB methods, i.e. having positional arguments external to the literals, like

 .addItem "column1","column 2", "column3"

No confusion about what is being passed: i.e.,

 .additem "Budget : " &  StringwithComma, "column2"

is not this

 .additem "Budget : " & StringbeforeComma, StringafterComma

or this

 .additem "Budget : " & StringBeforeComma, , StringAfterComma

The original sin here was putting a delimiter inside a literal. That is what has caused and continues to cause all sorts of unnecessary confusion.

Using " or ' around strings is pretty standard.

I can see using extra delimiters when passing arguments to an external processor (eg SQL engine) as a way to reconcile different flavours of the language. Internally in VB/VBA ? It's about as smart as mending a hole in a sock by putting another sock over it.

Here is what one guru suggested as a solution:

To use the existing Access listbox, the preferred on- simply perform a test on the string you are about to add to see if it contains a comma.

If Instr(strString,",") > 0 Then strString = """" & strstring & """"
IfMe.ListBox.AddItem StrString

Oh, well !

Best,
Jiri

 

[joemach]

Thanks for that, now it makes sense. Don't know why I didn't think of that.

There is nothing to fix. It works perfectly when applied correctly.

You tried it with a ' at one end, and it failed. If you had put a ' at both ends it would probably have worked as well.

You were trying to pass in Budget for 2012: 680,190 not "Budget for 2012: 680,190" or 'Budget for 2012: 680,190'.

Access basically threw away the " it was using to make up the string, it needed the extra " or ' to define what was passed as a string.

Normally you can pass multiple columns in one line.

"'column 1', 2, 'column 3'"

Or

"'column 1'; 2; 'column 3'"

That would be interpreted:
First column a string "column 1"
Second column a number 2
Third column a string "column 3"

 

[nanscombe]

It's an odd one. Sorry, I didn't mean any offence earlier Jiri.

It's strange that .AddItem was only added in Access 2002 because I spent 11 years working in Access 95 + 97 yet it seemed so familiar. I then jumped to just playing around, to keep my hand in, with 2007 and 2010.

It's been 6 years since I did any serious development and some of it involved populating Listboxes.

I guess it just became second nature to build long strings containing numbers and quote delimited text and passing the whole lot in as the RowSource.

 

[Solo712]

It's an odd one. Sorry, I didn't mean any offence earlier Jiri.

It's strange that .AddItem was only added in Access 2002 because I spent 11 years working in Access 95 + 97 yet it seemed so familiar. I then jumped to just playing around, to keep my hand in, with 2007 and 2010.

It's been 6 years since I did any serious development and some of it involved populating Listboxes.

I guess it just became second nature to build long strings containing numbers and quote delimited text and passing the whole lot in as the RowSource.

No offence taken, Nigel. It's just that I shrink instinctively from the kind of 'design' as is on display with the AddItem. Of course, that's not your problem. You got used to it and you don't question it because it works. I have issues with it. In the post I linked to above, the problem was made explicit. In a well-designed Access VBA you would not need to create specific code to verify content of a string to satisfy the interpreter. It's one example of generating redundant code. Mind you, it may not as bad as the handling of NULLs but still. There are sure ways to improve the product.

Best,
Jiri