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,387 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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

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…
The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Familiarize people with the process of utilizing SQL Server stored procedures 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 Micr…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

860 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