create list box from field with comma separated items

[mdavid]

Hi,


I have a table with a field of data type "Short Text" that contains a list of comma separated items - and my regional settings specify "," as the list separator.
I would like to display this field as a list box on the form that displays the row from the table with the items appearing as a list without the commas, instead I'm getting a single line of comma separated values - as they appear in the table field.

Thanks for any help

David

[CJ_London]

presumably you have set the rowsource type to value list? and set the columncount to 1 etc

assuming your listbox is bound to the short text field, in the recordsource to the form bring though a calculated value using the replace function to replace commas with semi colons

[Micron]

Without a listbox column that contains some sort of meaningful value, I envision a problem here. The first record may contain 1,2,3 and the next record 2,3,4. You will get repeating numbers that by themselves probably won't offer many clues as to which 2 is which.

[mdavid]

Hi, Thanks for your replys.
Is semi-colon the list separator character in Access?
Is it possible to show a field that contains a list of values in a listbox? Sorry for the dumb questions. Can you point me to a tutorial that does this

Thanks
David

[June7]

You get a single value of comma separated characters because that's exactly what is in the field, just a string of characters - commas are characters. If they weren't accepted as characters, could not have entries like: "We visited Mary, James, and George."

[Micron]

The semi colon is a separator for value lists and column widths for controls that can have columns. Don't confuse it with the system setting for distinguishing between values in a text type of file.

[davegri]

Do you want the values separated by commas to show as columns in the listbox, or as rows?
You realize that the listbox values will change for each record shown on the form since the listbox content is tied to a field in a single record.
If the number of csv values varies from record to record and you want the values to be displayed as columns, you will have to have code to modify the columcount property of the listbox for each record.
If you still want to go this route and see a little DB that attempts to handle this situation, let me know and I'll post it.

[mdavid]

Hi Dave,
Got off to a bad start on this forum, but you've help me see the error of my ways, I now understand that for a field that has multiple values eg. a field for "favorite books" I need a separate table with 2 fields: primary key of the parent table and the book title - does this second table also need a primary key?
I'd be interested to see the DB that handles my current situation, but more importantly how would you go about creating a second table of single items from the multiple values field.
Thanks for your help
David

[CJ_London]

but more importantly how would you go about creating a second table of single items from the multiple values field.

you would need to use VBA and the split function to separate out the different values. Something like

dim rst as DAO.Recordset
dim arr() as string
dim I as integer

set rst=currentdb.openrecordset("SELECT myCSVField FROM myTable")

While not rst.EOF
    if rst!myCSVfield<>"" then
        arr=split(rst!myCSVField,",")
        for I=0 to ubound(arr)-1
            currentdb.execute("INSERT INTO myNewTable (BookName) VALUES('" & arr(I) & "')")
        next i
    end if
    rst.Movenext
Wend

you would have created your table manually e.g.

myNewTable
bookPK autonumber
bookName text

[mdavid]

Hi Dave,
Got off to a bad start on this forum, but you've help me see the error of my ways, I now understand that for a field that has multiple values eg. a field for "favorite books" I need a separate table with 2 fields: primary key of the parent table and the book title - does this second table also need a primary key?
I'd be interested to see the DB that handles my current situation, but more importantly how would you go about creating a second table of single items from the multiple values field.
Thanks for your help
David

[mdavid]

Hi been Googling a bit and came across Junction tables, which sounds like what I'm looking for. My situation is that I'm creating a plants db, I have a Species name which is unique to each plant, but I have 4 columns for the names in English, Hebrew, Arabic and Other Names each of which can contain multiple values, so would I need to create 4 junction tables, or is there a method where I could create 1 Junction table for all 4 multi value columns?
Thanks for any help
David

[CJ_London]

you would have one table but with an additional column to indicate language

[mdavid]

Hi Ajax, Thanks for your VBA and the split function think that's the way I'll be going.
Don't understand your last comment

you would have one table but with an additional column to indicate language

Does that mean 1 table for all 4 values, wouldn't the column header indicate the language, like so:

Species Name English Names Hebrew Names Arabic Names Other Names

What am I missing?

[CJ_London]

by using language as a column header (field name) you are building data into your table design - simply, if you added another language (e.g. Latin) then you would need to modify your table and all the resultant forms, reports and possible code. Google/Bing 'Normalisation' to get a greater understanding

with the fuller explanation you have provided your tables would be structured something like

tblSpecies
SpeciesPK autonumber
SpeciesName text

tblLanguages
LanguagePK autonumber
LanguageName text

tblAlternativeNames
AltNamePK autonumber
SpeciesFK long- links to tblSpecies
LanguageFK long - links to tblLanguages
AlternativeName text

note my naming convention PK=Primary Key, FK = Foreign or Family Key. PK's are unique and should never be seen by the user, FK's can have duplicates and link back to the relevant PK - and are also never seen by the user.

Don't forget tables are for storing data and you should never be looking at tables directly (except during development). You use forms and reports. Tables should be designed for easy return and manipulation of data, not for presentation

[mdavid]

Ajax very much appreciate your explanation, beginning to see the light.
Don't see any way to acknowledge that you answered my question?

One question why do I need the tblLanguages? In tblAlternativeNames!LanguageFK I'd just enter the language name instead of a link to tblLanguage, does that not save a table and reduce the level of complexity?