concatenate function

[timbits35]

Hi,

I am trying to use this concatenate function, but I am getting an error in the SQL and I don't know how to fix the SQL statement because I have a junction table. Here are my tables.

I am trying to create a list of books with the authors on the same line separated by commas.

tbldoctrine
DoctrineID - primary key
title

tblauthors
AuthorID - primary key
lname

tblJncdocauth
Doctrine ID - foreign key
AuthorID - foreign key

Here is my attempt at SQL

SELECT tbldoctrine.title, ConcatRelated("lname","tblauthors","DoctrineID = " & [DoctrineID]) AS Expr1
FROM tbldoctrine INNER JOIN (tblauthors INNER JOIN tblJncdocauth ON tblauthors.AuthorID = tblJncdocauth.AuthorID) ON tbldoctrine.DoctrineID = tblJncdocauth.DoctrineID
ORDER BY tbldoctrine.title;

I am getting an error that DoctrineID could relate to more than 1 table. I don't know how to relate the tables so this works correctly.

Here is my function

Option Compare Database
Public Function ConcatRelated(strField As String, _
strTable As String, _
Optional strWhere As String, _
Optional strOrderBy As String, _
Optional strSeparator = ", ") As Variant
On Error GoTo Err_Handler
Dim rs As DAO.Recordset 'Related records
Dim rsMV As DAO.Recordset 'Multi-valued field recordset
Dim strSql As String 'SQL statement
Dim strOut As String 'Output string to concatenate to.
Dim lngLen As Long 'Length of string.
Dim bIsMultiValue As Boolean 'Flag if strField is a multi-valued field.

'Initialize to Null
ConcatRelated = Null

'Build SQL string, and get the records.
strSql = "SELECT " & strField & " FROM " & strTable
If strWhere <> vbNullString Then
strSql = strSql & " WHERE " & strWhere
End If
If strOrderBy <> vbNullString Then
strSql = strSql & " ORDER BY " & strOrderBy
End If
Set rs = DBEngine(0)(0).OpenRecordset(strSql, dbOpenDynaset)
'Determine if the requested field is multi-valued (Type is above 100.)
bIsMultiValue = (rs(0).Type > 100)

'Loop through the matching records
Do While Not rs.EOF
If bIsMultiValue Then
'For multi-valued field, loop through the values
Set rsMV = rs(0).Value
Do While Not rsMV.EOF
If Not IsNull(rsMV(0)) Then
strOut = strOut & rsMV(0) & strSeparator
End If
rsMV.MoveNext
Loop
Set rsMV = Nothing
ElseIf Not IsNull(rs(0)) Then
strOut = strOut & rs(0) & strSeparator
End If
rs.MoveNext
Loop
rs.Close

'Return the string without the trailing separator.
lngLen = Len(strOut) - Len(strSeparator)
If lngLen > 0 Then
ConcatRelated = Left(strOut, lngLen)
End If
Exit_Handler:
'Clean up
Set rsMV = Nothing
Set rs = Nothing
Exit Function
Err_Handler:
MsgBox "Error " & Err.Number & ": " & Err.Description, vbExclamation, "ConcatRelated()"
Resume Exit_Handler
End Function


Can you help me fix this?
Thank you,
Liane

 

[GinaWhipp]

No, it's not asking you to relate the tables. It is confused because you are using the sme field name in two different tables, try...

SELECT tbldoctrine.title, ConcatRelated("lname","tblauthors","tblauthors.DoctrineID = " & [tblJncdocauth]![DoctrineID]) AS Expr1
FROM tbldoctrine INNER JOIN (tblauthors INNER JOIN tblJncdocauth ON tblauthors.AuthorID = tblJncdocauth.AuthorID) ON tbldoctrine.DoctrineID = tblJncdocauth.DoctrineID
ORDER BY tbldoctrine.title;

To avoid issues like this, have a look at...
http://www.access-diva.com/d1.html

 

[timbits35]

Hi,

Thank you for your response. I tried to modify the query to this :

SELECT tbldoctrine.title, ConcatRelated("lname","tblauthors","tbldoctrine.DoctrineID = " & [tblJncdocauth]![DoctrineID]) AS Expr1
FROM tbldoctrine INNER JOIN (tblauthors INNER JOIN tblJncdocauth ON tblauthors.AuthorID=tblJncdocauth.AuthorID) ON tbldoctrine.DoctrineID=tblJncdocauth.DoctrineID
ORDER BY tbldoctrine.title;


But now I get this error.
Run-time error '3061'. Too few parameters. Expected 1."

You had written to put tblauthors.DoctrineID but this is not a field in the authors table, so I am not sure what to put.

Thank you
PS Please note that there is actually no space here tbldoctrine.Do ctrineID, it is just the computer display showing the space

 

[GinaWhipp]

Did you copy the formula correctly from Allen Browne's site? I just tried it and it works. Try copying and pasting again...
http://allenbrowne.com/func-concat.html

 

[timbits35]

Hi,

I think the problem is the select statement. This is not just a 1 to many relationship with 2 tables. This is a many to many relationship involving a junction table and I have no idea which tables and fields to use in the SQL statement. Here are my tables. The 1 side is the tbldoctrine. technically the many side is the authors table, but since 1 DoctrineID can have many authors and 1 author can be related to many DoctrineID, I have the junction table. So I don't know what to use in the SQL statement.

tbldoctrine
DoctrineID - primary key
title

tblauthors
AuthorID - primary key
lname

tblJncdocauth
Doctrine ID - foreign key
AuthorID - foreign key

This is the SQL that is currently causing the 3061 error. I had first debugged the function all by itself with no errors.

SELECT tbldoctrine.title, ConcatRelated("lname","tblauthors","tbldoctrine.Do ctrineID = " & [tblJncdocauth]![DoctrineID]) AS Expr1
FROM tbldoctrine INNER JOIN (tblauthors INNER JOIN tblJncdocauth ON tblauthors.AuthorID=tblJncdocauth.AuthorID) ON tbldoctrine.DoctrineID=tblJncdocauth.DoctrineID
ORDER BY tbldoctrine.title;

Thank you.

 

[GinaWhipp]

I got it to work with no issues and with the junction table... That is why I am suggesting recopying the code. Are you saying you tried that and it still results in an error?