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.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Replace("yourstring", ",", ";")