Link to home
Create AccountLog in
Avatar of robtrue
robtrueFlag for United States of America

asked on

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.
Avatar of pdd1lan
pdd1lan

you can use replace function to replace "," to ";"

Replace("yourstring", ",", ";")
Avatar of robtrue

ASKER

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.
SOLUTION
Avatar of peter57r
peter57r
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
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
 
  Next
 
  debug.Print myStr
ASKER CERTIFIED SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account