MS Access: lookup values for string-based multi-value field

Question

Here is my situation:

linked table (so I can't actually modify the column config) has a column with a comma-separated list of values. this is a text string -- not a true multi-value field.
I need to map those values to a lookup table and return a comma-separated list using the lookup values

So let's say a row has the values: A,B,C in this column. The mapping table maps: A|1, B|2, C|3. The resulting column in my query should list 1,2,3 (the mapped values).

I can add a column in my query, indicate the display control is combo box (which is what you would typically do for a multi-value field), select my row source, and bind the columns -- but only rows with a single option value will map correctly. If it has a comma-separated list, per the above example, it does not map the values. How would I go about this mapping?

Please [edit] your question to clarify: Is the source column a plain Text field with a comma-separated string, or is it true multi-valued field (as created by the "Lookup Wizard" in Access)? — Gord Thompson, Apr 08 '16 at 18:47
I have a query in which I want to output the string "1,2,3" as mapped from the values "A,B,C" in a table column. — lcdservices, Apr 08 '16 at 20:00

score 1 · Answer 1 · answered Apr 08 '16 at 23:18

To display such results, you would need a VBA function.

The code would look like:

Public Function TransComma(MyList As Variant) As String

   Dim TransList        As Variant
   Dim Token            As Variant
   Dim result           As String

   If IsNull(MyList) = False Then
      TransList = Split(MyList, ",")
      For Each Token In TransList
         If result <> "" Then result = result & ","
         result = result & DLookup("Color", "tblColors", "ID = " & Trim(Token))
     Next Token
     TransComma = result
  End If

End Function

The above code is to be placed in a standard code module.

Now in a form you can place a text box, and set the control source to this

=(TransComma([name of field]))

And for a report, or sql query, simply go:

Select firstName, LastName, ColorList, TransComma([ColorList) 
as translated from tblCustomers

So once you build this translate function, it can be used translate the numbers to some text.

MS Access: lookup values for string-based multi-value field

1 Answers1