How to delete the comma when multiple values are allowed

[AccessDanger]

I have several fields that allow multiple values, and the output adds a comma between the records. The fields need to be separated by a period or a blank space (not a comma). How can I change this?

Windows 8
Access 2013

[orange]

Are you familiar with Normalization?

[AccessDanger]

That video isn't about Normalization, however, i did watch one about normalization ( https://www.youtube.com/watch?v=0VCfiU07DWo ).

My tables are set up and linked like he describes, but I chose to allow multiple values (I have Access 2013 - the multiple values option was not an option in the version of Access I used previously - in case that matters?).

When I choose a field [itemnum], for example, a dropdown box will appear with available item numbers. Check the boxes of corresponding item numbers, and the data appears as follows:
123456, 456, 78911

I need the data to appear as:
123456 456 78911


I hope that makes sense. I'm kind of new to Access (if you can't tell).
Thanks!

[ItsMe]

Perhaps you could implement the Replace() function. However, I do not believe the Replace function will work on an entire DB. It is a function to be used with strings.

[June7]

If you are using a multi-value field, the comma separator is intrinsic to that functionality and AFAIK cannot be altered. You should fully understand multi-value fields and their limitations before committing to their use. I NEVER use them.

http://office.microsoft.com/en-us/ac...001233722.aspx
http://office.microsoft.com/en-us/ac...010149297.aspx

[AccessDanger]

If you are using a multi-value field, the comma separator is intrinsic to that functionality and AFAIK cannot be altered. You should fully understand multi-value fields and their limitations before committing to their use. I NEVER use them.

http://office.microsoft.com/en-us/ac...001233722.aspx
http://office.microsoft.com/en-us/ac...010149297.aspx

I found another thread suggesting not to use "MVF" and wondered if that was the same as Multi-valued fields, and apparently it is. I'll redesign the database - i wasn't super happy with it anyway. Thank you for your reply!

[orange]

@AccessDanger,

Sorry about the Normalization link--I copied the link from one of ItsMe's posts. She talked abut everything but Normalization per se.
Glad you found one to your liking.

I've only met one person who uses MVF and thinks they're great. Most people/developers will tell you to use traditional table. M$oft thinks this is a feature, but it's more gimmick in my view, and not supported in other products.

Good luck with your project.

PS:

Here's the link I usually suggest for database design and Normalization. The video quality is a little lacking but the content isnot bad and all videos deal with the same topic---Customer Order OrderDetail Item. Other links are available on the page.

[AccessDanger]

@AccessDanger,

Sorry about the Normalization link--I copied the link from one of ItsMe's posts. She talked abut everything but Normalization per se.
Glad you found one to your liking.

I've only met one person who uses MVF and thinks they're great. Most people/developers will tell you to use traditional table. M$oft thinks this is a feature, but it's more gimmick in my view, and not supported in other products.

Good luck with your project.

PS:

Here's the link I usually suggest for database design and Normalization. The video quality is a little lacking but the content isnot bad and all videos deal with the same topic---Customer Order OrderDetail Item. Other links are available on the page.

Thank you very much!
I've designed a few databases, and almost always have to restart due to design. Hopefully these links will help me organize the db prior to creation.

[June7]

If you store the data in conventional table as individual records but in a report want to show the data from multiple records in a single string, review: http://allenbrowne.com/func-concat.html