none
How to extract text content from multiselect combo box field ?

    Question

  • I'm looking for a way to extract text content from multiselect combo box field.

    I have access files from external source with multiselect combo box and I need to extract data to text box same as the way it displays, for example: "Selection1, Selection2, Selection3".

    I use query to extract [Combobox].Value, I have the text content for each selection but when I make table, the content turn to number. I try ConcatRelated function and the result is number.

    Extract combo box content using form is possible but it's not my case.

    Is there any way to extract combo box text content without form ?

    I appreciate any help.

    Monday, April 15, 2013 1:54 AM

Answers

  • But if I export, copy or link the table to a new access file, I only see id number. This seem to be my case since I don't have source table for combo box.

    Is there any way to solve this problem ?
    For exporting, export the query's result table, not the original base table.  You can do this in exactly the same way as exporting a base table by using the query name instead of the table name when you undertake the export.

    If you need to create a link in another access front end file you would create links to both tables, the referenced table and the referencing table, and then create a query which joins the two linked tables in the other front end file.


    Ken Sheridan, Stafford, England

    Thursday, April 18, 2013 11:16 AM

All replies

  • I am not familiar with a multi-select combobox in Access. How did you set this up?


    -Tom. Microsoft Access MVP

    Monday, April 15, 2013 3:20 AM
  • I don't set up the tables structure. They are access files that I got from other people to work on the data. But as I know those fields point to a table to get data from (2-3 columns combo box). However, I don't have that combo box source table.

    For your question about setting up multi-select combo box, create lookup field in table design view, point to a table field or create your own value list, check "allow multiple values". In Lookup tab, you can choose list box or combox box.

    Monday, April 15, 2013 4:05 AM
  • Ah, the evil lookup field. Sorry, I can't help you there. This is not a relational construct.

    MSFT added support for it because of the support for SharePoint, but I can't follow them there.


    -Tom. Microsoft Access MVP

    Monday, April 15, 2013 4:10 AM
  • You say you want to extract them the way they are shown in a fiield "Selection1, Selection2", but then you say you use [Field].Value. What is the final goal? To get them "as is" just use [Field] w/o .Value property.

    Andrey V Artemyev | Saint-Petersburg, Russia
    Russian blog artemyev.biztoolbox.ru

    • Proposed as answer by KCDW Tuesday, April 16, 2013 5:00 PM
    • Unproposed as answer by D.MSK Thursday, April 18, 2013 2:49 AM
    Monday, April 15, 2013 6:01 AM
  • For example, in the table it shows as below just like normal multi-select combobox (dropdown to choose)

    ID     ComboBoxField                                            TextField (What I want)

    1       Selection1, Selection2, Selection3               Selection1, Selection2, Selection3

    2       Selection7, Selection9                                 Selection7, Selection9

    ....

    What I want to archive is to bring the same values, format ("," as separator) to TextField. I couldn't find a way to direct update the value to TextField. Using query and with [ComboBoxField].Value is just my attempt to extract text content (if it's successful, I can use ConcatRelated function to merge all values to 1 record to match the format I want above). Query show:

    ID     ComboBoxField

    1      Selection1

    1      Selection2

    1      Selection3

    2      Selection7

    2      Selection9

    However, when I use make table query, instead of showing text content, it shows random number

    ID     ComboBoxField

    1      12

    1      14

    1      15

    2      23

    2      26

    I tried to make table with same table format query, use [ComboBoxField].Value to make table with each combo box value is 1 record with the goal to get text content, but all I get after making tables is number instead of text content.

    Monday, April 15, 2013 7:58 AM
  • Try with the following code:

    [ComboBoxField].SetFocus

    Me.TextBox1.Value = Me.[ComboBoxField].Text

    This will copy the text in the combobox to a textbox.

    Good day.


    Yoyo Jiang[MSFT]
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Tuesday, April 16, 2013 8:59 AM
    Moderator
  • Just use a DLookUp in the Text Field.

    This will get the values from the Table and display them in the Textbox.

    Real World example

    Edit 20130416 11:48 AM Added Picture to show multi-select cbo next to Fee in Dollars txtbox as demo

    =IIf([AccountingID] Is Null,Null,Nz(DLookUp("SumOfCHAMOUNT","FEECH","AccountingID = " & [AccountingID]),0))


    Chris Ward


    • Edited by KCDW Tuesday, April 16, 2013 4:49 PM
    Tuesday, April 16, 2013 2:29 PM
  • Did you try what Andrey said?  Just change your query from:

        ComboBoxField.Value
    to:
        ComboBoxField

    Note that a multi valued field's Value property returns a recordset, not a value.

    Tuesday, April 16, 2013 4:36 PM
  • Using Andrey's suggestion in the Query,

    the SQL is

    SELECT Accounting.AccountingID, Accounting.CHEFEE
    FROM Accounting;

    the result is

    Your issue may have to do with setting the ID field size to Zero


    Chris Ward


    • Edited by KCDW Tuesday, April 16, 2013 5:01 PM
    Tuesday, April 16, 2013 4:59 PM
  • As you appear  to have been lumbered with a database which has used multi-valued fields you clearly have no choice in the matter, but what you do is to create a query which joins the tables on the relevant columns in much the same way as you would with a regular one-to-many relationship and return the text column from the referenced table.  If we assume for instance a table Contacts which includes a multi-valued field Cities which references the CityID key of a Cities table the query would be along these lines:

    SELECT Contacts.FirstName, Contacts.Lastname, Cities.City
    FROM Cities INNER JOIN Contacts
    ON Cities.CityID = Contacts.Cities.Value;

    It would be necessary to use a RIGHT JOIN if there are any rows in Contacts with a NULL at the city column position, which you'd want returned.  Note that the reference to the multi-valued field I the relationship is followed by .Value.  This is in fact a disguised many-to-many relationship type, which conventionally would be modelled by three tables, so the query would be:

    SELECT Contacts.FirstName, Contacts.Lastname, Cities.City
    FROM Cities INNER JOIN (Contacts INNER JOIN ContactCities
    ON Contacts.ContactD = ContactCities.ContactID)
    ON Cities.CityID = ContactCities.CityID;

    where ContactCities is the table which models the many-to-many relationship type by resolving it into two one-to-many relationship types.  Unless whoever designed the database originally had a specific need to use a multi-valued field in order to interface with SharePoint in the very limited context that requires this feature,  it is unfortunate that they did not use the conventional model.

    Ken Sheridan, Stafford, England

    Tuesday, April 16, 2013 5:27 PM
  • PS:  If you need to return the text values as a delimited list rather than as values in separate rows you can do so by creating another query which using a small variation on my original:

    SELECT Contacts.ContactID, Contacts.FirstName, Contacts.Lastname, Cities.CityID
    FROM Cities INNER JOIN Contacts
    ON Cities.CityID = Contacts.Cities.Value;

    You can then create another query based on that query which returns DISTINCT Contacts.ContactID, Contacts.FirstName, Contacts.Lastname values and uses a concatenation function in a computed column to return a delimited list of the city names.  You'll find an example of such a function in Concat.zip in my public databases folder at:

    https://skydrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    My function uses the very efficient GetString method of the ADO recordset object, but there are others available which use DAO, albeit less efficiently, though unless you have very many rows to handle the difference is unlikely to be very perceptible.

    Ken Sheridan, Stafford, England

    Tuesday, April 16, 2013 5:37 PM
  • PPS:  You could get away without the join in the first query:

    SELECT Contacts.ContactID, Contacts.FirstName, Contacts.Lastname, Cities.Value
    FROM Cities;

    Then base a SELECT DISTINCT query calling the concatenation function on the above, passing the name of the Cities table into the function along with the other relevant parameters to return a delimited string of city names.

    Ken Sheridan, Stafford, England

    Tuesday, April 16, 2013 5:45 PM
  • Sorry for late reply.

    Thanks Yoyo Jiang, KCDW and Ken Sheridan for your time and instructions.

    It will work if I directly edit records under form or datasheet view. However, I don't edit/modify/select anything from combo box by myself. The table I have (access file from other people/software output) already has all information filled, all I do is extract needed information from the table for certain uses. Especially, I don't have the source table that the combo box field uses as its list. Otherwise, I can match ID and extract text value easily.

    I have done some testing and know that if I create a access file and create combo box in that access file, the value [ComboBox].Value in query is text content and I can use make table or update query to update text content to text field easily. But if I export, copy or link the table to a new access file, I only see id number. This seem to be my case since I don't have source table for combo box.

    Is there any way to solve this problem ?
    Thursday, April 18, 2013 2:45 AM
  • But if I export, copy or link the table to a new access file, I only see id number. This seem to be my case since I don't have source table for combo box.

    Is there any way to solve this problem ?
    For exporting, export the query's result table, not the original base table.  You can do this in exactly the same way as exporting a base table by using the query name instead of the table name when you undertake the export.

    If you need to create a link in another access front end file you would create links to both tables, the referenced table and the referencing table, and then create a query which joins the two linked tables in the other front end file.


    Ken Sheridan, Stafford, England

    Thursday, April 18, 2013 11:16 AM