Getting the value not the ID

I have a table tblInvestigators which contains a lookup field to display a list of names

A grant may have more than 1 investigator.

A requirement of my project is to list all investigators in a single cell next to the grant details, so i'd have:

Grant A | Name A, Name B, Name C etc.

I have a VBA module that concatenates the investigators into 1 cell as follows:

 'Concat Returns lists of items which are within a grouped field
 Public Function c(strID As String, strAddMe As String) As String开发者_C百科
     Static prevID As String
     Static strFinal As String
     Static strLastAdded As String

If (strID = prevID And strAddMe <> strLastAdded) Then
          strFinal = strFinal & ", " & strAddMe
Else
          prevID = strID
          strLastAdded = strAddMe
          strFinal = strAddMe
End If
     c = strFinal

 End Function

And an access query that calls it (SQL):

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;

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?

Chris

It is never a good idea to use look-up fields: http://www.mvps.org/access/lookupfields.htm

It is disguising the standard way of getting the results you want, which is to use a query to join the ID to the look-up table and return the description.

Have a look at this Does MS access(2003) have anything comparable to Stored procedure. I want to run a complex query in MS acceess