title: Getting the value not the ID
body: <p>I have a table tblInvestigators which contains a lookup field to display a list of names</p>
  
  <p>A grant may have more than 1 investigator.</p>
  
  <p>A requirement of my project is to list all investigators in a single cell next to the grant details, so i'd have:</p>
  
  <p>Grant A | Name A, Name B, Name C
  etc.</p>
  
  <p>I have a VBA module that concatenates the investigators into 1 cell as follows:</p>
  
  <pre><code> 'Concat Returns lists of items which are within a grouped field
   Public Function c(strID As String, strAddMe As String) As String
       Static prevID As String
       Static strFinal As String
       Static strLastAdded As String
  
  If (strID = prevID And strAddMe &lt;&gt; strLastAdded) Then
            strFinal = strFinal &amp; ", " &amp; strAddMe
  Else
            prevID = strID
            strLastAdded = strAddMe
            strFinal = strAddMe
  End If
       c = strFinal
  
   End Function
  </code></pre>
  
  <p>And an access query that calls it (SQL):</p>
  
  <pre><code>SELECT g.grant_id, Max(c(g.grant_id,tblInvestigators.investigator)) AS Expr1
  FROM tblGrants AS g LEFT JOIN tblInvestigators ON g.grant_id = tblInvestigators.grant_id
  WHERE (((g.grant_id)=6))
  GROUP BY g.grant_id;
  </code></pre>
  
  <p>When I run this, it returns a comma separated list, but it is a list of the ID numbers from the look up column (tblInvestigators.investigator)rather than the names. How can I get the names?</p>
  
  <p>Chris</p>