I am trying to concatenate 2 fields from different tables in Access 2007.
I have few constraints..... first I cannot change the existing vba code second, I cannot use the group by as the person that ask for the report does not like to display the fields vertically, but he wnat them in a single line... so...
I found in the existing database a module called concatenate(I attached the code) and I am trying to use in this way
FROM Projects INNER JOIN Project_Applicants ON Projects.ID =" & [Project_Applicants.Projec
t_ID] & "" , ",")
BUT i GET A runtime error..... JOIN EXPRESSION NOT SUPPORTED
I am not an expert on SQL and I cannot spot my mistake.
the goal here is to show on a single line the Applicants names(they sometime are more than one) and the Principal investigator name.
I also have to deal with empty fields in the above query.
I am using Access 2007
Function Concatenate(pstrSQL As String, Optional pstrDelim As String = ", ") _
Dim rs As New ADODB.Recordset
rs.Open pstrSQL, CurrentProject.Connection, _
Dim strConcat As String 'build return string
If Not .EOF Then
Do While Not .EOF
strConcat = strConcat & _
.Fields(0) & pstrDelim
Set rs = Nothing
'====== uncomment next line for DAO ========
'Set db = Nothing
If Len(strConcat) > 0 Then
strConcat = Left(strConcat, _
Len(strConcat) - Len(pstrDelim))
Concatenate = strConcat