Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2011-02-14
5
Medium Priority
?
1,510 Views
Last Modified: 2012-05-11
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.
0
Comment
Question by:robtrue
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 8

Expert Comment

by:pdd1lan
ID: 34887916
you can use replace function to replace "," to ";"

Replace("yourstring", ",", ";")
0
 

Author Comment

by:robtrue
ID: 34888107
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.
0
 
LVL 77

Assisted Solution

by:peter57r
peter57r earned 2000 total points
ID: 34888114
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.
0
 
LVL 8

Expert Comment

by:pdd1lan
ID: 34888550
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
0
 
LVL 77

Accepted Solution

by:
peter57r earned 2000 total points
ID: 34888706
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.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

618 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question