[MS Access 2003 Look up field and modules] Getting the value not the ID

Question

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

Answer 1

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 http://stackoverflow.com/questions/230241/does-ms-access2003-have-anything-comparable-to-stored-procedure-i-want-to-run/231570#231570