• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1618
  • Last Modified:

How do I change an Access 2007 multivalued field separator from the default comma to a semicolon?

I have a table in Access 2007 that has a multivalved field.  By default values listed in a multivalved field are separated by commas.  I would like to change the default from a comma to a semicolon.  How can this be done?  The reason for wanting to change to a semicolon is that the data values contain embedded commas.
  • 2
  • 2
2 Solutions
you can use replace function to replace "," to ";"

Replace("yourstring", ",", ";")
robtrueAuthor Commented:
No, this will also change the embedded commas in the data.  Look at this way.  Imagine a user entering data via a datasheet view. When the user clicks on the multivalued field, a lookup combo (or list) appears.  The user selects two are more items.  The selected items appear in the field as a comma (by default) delimited string.  I would to set the delimiter to a semicolon.
I don't think there is  any way of changing them, because they don't really exist.  
The presentation of a multi-valued field is an internal Access process - the real values are in a separate junction table.  There is nothing which actually contains the values separated with commas.

I understand your issue with this, but I don't think there is a solution.
Maybe I miss understand the question, but when you select item(s) in list box..
you can capture input value and modify as the way you want:

example:  list4: listbox

  Dim var As Variant
  Dim strDelimit As String
  Dim myStr As String
  strDelimit = ";"
  For Each var In List4.ItemsSelected
    If Not IsNull(var) Then
        myStr = myStr & List4.ItemData(var) & strDelimit
    End If
  debug.Print myStr
In A2010, the form control has a 'Separator character' property allowing you to change the separator to Newline or ;

I don't know if this was available in A2007.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now