Semicolons within listboxes

[Seeker]

I have a form with a two-column listbox on it. The first column shows the invoice number and the second column displays the (text) description. The user types some text into a textbox, clicks Search, and the matching results display in the listbox. Recently a record was added where the description contained a semicolon. The description text beyond the semicolon does not appear in the listbox. Subsequent records display... oddly. For example,

Table1
001 | Apple
002 | Banana; Cantelope
003 | Date

Listbox1
001 | Apple
002 | Banana;003;

Any suggestions on how to make this display correctly? The screwup itself suggests semicolons can appear in multi-column listboxes.

 

[RuralGuy]

How about running an Update query that replaces the ";" with a comma ","?

 

[Seeker]

Comma nee semicolon

I was hoping to display the info as entered but that's what I'll do if needs be.

Thanks.

 

[RuralGuy]

Maybe someone else will have a different idea.

 

[RoyVidar]

How are you populating your listbox? On a small test here, there's no problem with table/query row source, but when using value list, semicolon is column/row separator.

 

[Seeker]

Alternatives

lstResults.AddItem [Inv] & ";" & [Desc]

 

[Dreamweaver]

You could just replace it for the list IE Replace([Desc],";",",")

Think I got the replace round the right way

Mick

 

[Moniker]

You answered your own question. You said you are adding items like this:

lstResults.AddItem [Inv] & ";" & [Desc]

The semi-colon is the separator, so if [Desc] has a semi-colon, it will break your process. Either do what others have already suggested (replace the semi-colon with something else using any number of methods), or switch to a Table/Query as the RowSourceType.

 

[Seeker]

I really appreciate all the feedback, but I didn't really answer my own question, Moniker, because I'm not asking WHY the semicolon breaks the process. I understand it signals the end of a column and thus the two column listbox returns odd information when asked to display three columns. And I see that replacing each semicolon with a comma will fix the issue but that solution has the downside of then displaying a comma and not a semicolon. The only reason I'm asking if anyone knows how it can be done is because after the listbox trips up on the record containing a semicolon the following listbox item actually displays semicolons in the description column. They appear where they ought not to be but they are there nonetheless.

Does anyone out there know how to achieve this intentionally?

 

[RuralGuy]

Have you tried doubling up the ";" and see if Access take the 1st one as an escape?

 

[Seeker]

Yeah I tried Replace([Desc], ";", ";;") and it fixes the display problems with subsequent records but doesn't change the cutoff.

Thanks for asking.

 

[RuralGuy]

Oh well. Good luck.

 

[boblarson]

Here's a radical concept to try since you seem to not be able to use add to list very easily.

Why not create a separate table to store your listbox values and then just set a query to pull those values and if you need to add values to the list, use code to add it to the table and then requery the listbox. That might solve your problem (just a suggestion).

 

[Seeker]

All kinds of people have all different levels of experience with Access and what's great about this forum is that anyone with enough knowledge to ask the right questions can usually get an answer. Unfortunately for the people replying, what's difficult about any forum of this kind is judging how much knowledge the poster has and responding accordingly. As such, I truly do appreciate everyone's efforts and it is not my intent to sound snippy in my replies to posts with helpful intents.

It may be impossible, in which case I'll likely get no further responses, but does anyone know how to use AddItem to intentionally create a listing in a multi-column listbox that displays a semicolon? Otherwise, I'll rewrite my code.

Thanks.

 

[BrettStah]

Have you tried putting quotes around the description part, to see if you can get the semi-colons in there?

 

[Seeker]

Yep, tried it: no good. But thanks.

 

[BrettStah]

Yep, tried it: no good. But thanks.

Hmmm... can you try (if you haven't already) putting quotes around each section of text that will become rows in the listbox?

Example of what I'm thinking of...

"one";"two";"milk;cake;beer;cookies"

In that example, I would expect that only the first two semi-colons would be treated as delimiters. But I've been wrong before.

 

[Seeker]

Yeah, I tried that too, just as a test. Thanks though.