Changing the character separator

[dmattarn]

First off, thanks to everyone who posts here and uses the site, it has been invaluable to my efforts to use access.

Currently I'm working on a report in Access 2013 and I have a multivalued field that stores employees names in the format "Last, First" and when I put it on my report it displays all the names as "Last1, First1, Last2, First2, Last3, First3" and so on. That's a lot of commas and it was suggested to me by my boss that I change access to use semicolons between the employees as he would like to keep the names as Last, First. How could i accomplish this?

Thanks in advance for the help,
Derek

 

[CJ_London]

multivalue fields are not really intended to be used for this purpose - intended more for selecting from a limited fixed number of options such a 'summer, winter, autumn, spring' or geographical locations.

The behaviour is fixed with a comma between choices, so you'll need to modify the choice field to be 'first1 last1' or perhaps 'last1>first1'

Multivalue fields are very restrictive and potentially inefficient (can't be indexed, can't be upsized for example) and most developers won't use them. You would be better to store the data in the standard method of a separate table and then you can display the data exactly as you require.

Alternatively, check out this link which you can adapt for a multivalue field http://allenbrowne.com/func-concat.html

 

[dmattarn]

I have a lot of things based off the "Last, First" field being the way it is, so I'll have to give that link a try as a workaround. Thanks for the help!

 

[vbaInet]

I would imagine that MS developed a mechanism for fast retrieval and fast data manipulation for this field, whether through indexing or some other sort of algorithm. And I think it's mentioned somewhere that it does index and create relationships.
Of course I'm not advocating using multi-valued fields, I'm just saying that multi-valued fields (in my opinion) use indexing and have relationships setup in the background.

Since the field is a recordset (i.e. Recordset2), like CJ said, you should be able to adapt the ConcatRelated() function to suit.

Welcome to AWF by the way.

 

[dmattarn]

Yeah I think that should work. After further thought about what I need to display, I will end up having to take into account a few other controls (i.e. whether or not the employee is still active) that decide whether or not a name is displayed in the Text Box.

 

[vbaInet]

To be honest, this is your best bet:

You would be better to store the data in the standard method of a separate table and then you can display the data exactly as you require.

 

[dmattarn]

I can easily create a query to attain just the data I need, but I will still need to concatenate them together with semicolons

 

[dmattarn]

Alternatively, check out this link which you can adapt for a multivalue field allenbrowneDOTcom/func-concat.html

this was an absolute lifesaver