Displaying a comma in a Listbox (value list) 1

[BoSoxFan]

I'm trying to display a comma (e.g. Spencer, Lori) in one column in an Access listbox via code. I've tried chr(44) and "","" etc. When I run my listbox.rowsource thru the debugger, it appears that my data will be displayed properly (I'm using ; as a delimiter), but Access seems to think that commas are also delimiters, and it throws "Lori" into the next column.
Here's an example of what I've been trying out.

Me.LstPosition.RowSource = as400.AuthRead!PRPOSN & ";" _
& sFirstHalf & "chr(44)" & sLastHalf & ";" & as400.PRSSN & ";"

 

[Frink]

Hallo,

Trick one that. I'm not sure you can.
What about creating a table of the names and basing the listbox on that?
It would be a bit slower, and you'd have to allow for multiple users in a multi user db, but it should work.

- Frink

 

[BoSoxFan]

Thanks for the quick response. I wish it were that simple - I'm actually reading data from an AS400 via ODBC. The problem field is "Job Description" (on the 400), where the description might (but usually doesn't) contain a comma, such as "Accountant, Jr".

I've cheated by converting the comma to a hyphen (which is good enough for me!), but my team lead would prefer to keep it a comma.

I can't fetch the data from the 400, store it in a table, then display it, because I have to keep this real time. If someone changes the 400, my Access program needs to know about it.

 

[Frink]

Hallo again,

Oh, I see. So why are you using code? Is there no way you can link to the data as a table and base your list box on it?

Sorry if this is a stupid question, I've not had much experience with ODBC, but I thought you could link to ODBC tables?

- Frink

 

[BoSoxFan]

Hi again,
I'm not overly familiar with ODBC myself (I've inherited this code), but I'm pulling data from three different tables on the AS400, then displaying one field from each table in the listbox. Trying to add a new table in SQL Server (it's an Access project) to handle all this would me more work than I want to get into, and I'm not sure it would work for all the scenarios that are possible (this code is total spaghetti to begin with).

I'm afraid I'm not sure how to clearly state all the problems I'm having, because I don't quite understand how this all works myself. I was hoping there was an easy way to tell Access not to use certain commas as delimiters, but to show them as commas.

If there isn't a simple solution - which it sounds like there isn't - I think I'll just stick with my kludge.

I really appreciate your quick responses - you have no idea how much it helps!

 

[ssecca]

Try this

Me.LstPosition.RowSource = as400.AuthRead!PRPOSN & ";'" _
& sFirstHalf & ", " & sLastHalf & "';" & as400.PRSSN & ";"

Please let me know if it works, It looks like it does in my test.

ssecca

 

[BoSoxFan]

You're my hero! Yes, that worked - thank you SO much. (And wouldn't you know it was something so easy - I didn't think about the single quotes).