Solved

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

Posted on 2011-02-14
5
1,364 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
  • 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 500 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 500 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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
FrontEnd tools to create web database application 7 59
Dlookup MSACCESS 5 25
Should I keep recordsets open? 3 24
Binding recordsets to a form 6 23
This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
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…

773 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