[AccessD] Concatenating question...

Hi group,
I am using a function created by Duane Hookom, 2003 about how to concatenate 
two fields in one to the following query

SELECT [Last_name] & " " & [Father_name] & " " & [First_name] AS onoma, 
T_ca_diagnosis.ca_diagnosis, RT_AM_metastaseis.kind_ca
FROM T_ca_diagnosis RIGHT JOIN (MT_basic_char INNER JOIN RT_AM_metastaseis 
ON MT_basic_char.AM = RT_AM_metastaseis.AM) ON T_ca_diagnosis.IDca_diagnosis 
= RT_AM_metastaseis.IDca_diagnosis;


What I want to do is to make a new field e.g. Diagnosis in which the data 
will appear concatenated like:

T_ca_diagnosis.ca_diagnosis01 (RT_AM_metastaseis.kind_ca01), 
T_ca_diagnosis.ca_diagnosis02 (RT_AM_metastaseis.kind_ca02), etc etc

The function is calling with:
Diagnosis: Concatenate("SELECT T_ca_diagnosis.ca_diagnosis & ' (' & 
RT_AM_metastaseis.kind_ca & ')' FROM ....... WHERE 
MT_basic_char.AM=RT_AM_metastaseis.AM]
and T_ca_diagnosis.IDca_diagnosis = RT_AM_metastaseis.IDca_diagnosis

But it doesn't work cause something is missing between FROM and WHERE and 
that's exactly the point I need your help...

Many thank's
/kostas


Function Concatenate(pstrSQL As String, _
        Optional pstrDelim As String = ", ") _
        As String
'Created by Duane Hookom, 2003
'this code may be included in any application/mdb providing
'   this statement is left intact

'======For DAO uncomment next 4 lines=======
'====== comment out ADO below =======
    'Dim db As DAO.Database
    'Dim rs As DAO.Recordset
    'Set db = CurrentDb
    'Set rs = db.OpenRecordset(pstrSQL)

'======For ADO uncomment next two lines=====
'====== comment out DAO above ======
    Dim rs As New ADODB.Recordset
    rs.Open pstrSQL, CurrentProject.Connection, _
    adOpenKeyset, adLockOptimistic
    Dim strConcat As String 'build return string
    With rs
        If Not .EOF Then
            .MoveFirst
            Do While Not .EOF
                strConcat = strConcat & _
                .Fields(0) & pstrDelim
                .MoveNext
            Loop
        End If
        .Close
    End With
    Set rs = Nothing
'====== uncomment next line for DAO ========
    'Set db = Nothing
    If Len(strConcat) > 0 Then
        strConcat = Left(strConcat, _
        Len(strConcat) - Len(pstrDelim))
    End If
    Concatenate = strConcat
End Function