Concatenating Records in aTable (1 Viewer)

[Kryst51]

I have a dilemma. I am trying to concatenate records in a table to display on a form or a report. I have figured out (using a function I found on the internet) how to concatenate the records for display. However, I need to attach the date to it. For instance:

AN     Date             Note          fkIDforanothertable
1    01/01/11  This is the first Note.         5
2    01/01/11  This is the second Note         1
3    01/01/11  This is the third Note          5
4    01/02/11  This is the fourth Note         1
5    01/02/11  This is the fifth Note          5

I would like it to display Like this in a text box if I was querying the fk number 5:

01/01/11 - This is the first Note. This is the third Note

01/02/11 - This is the fifth Note.

I have the following to put into a query. It works if I put it into the control source of a text box, but if I put it into a query it doesn't work I get an error.

This goes into my query:

ErrorDescription: Concatenate("SELECT ErrorNote FROM tblErrorNotes WHERE pkNCRIssueID =" & [fkNCRIssueID])

Here is the Concatenate function (I put a break at the beginning of the function:

Function Concatenate(pstrSQL As String, Optional pstrDelim As String = ". ", _
                     Optional pstrLastDelim As String = ".") _
                     As Variant
 
10    On Error GoTo Concatenate_Error
 
Dim rs As New ADODB.Recordset
20        rs.Open pstrSQL, cn
    Dim strConcat As String 'build return string
30        With rs
40      If Not .EOF Then
50          .MoveFirst
60          Do While Not .EOF
70              strConcat = strConcat & _
                .Fields(0) & pstrDelim
80              .MoveNext
90          Loop
100     End If
110     .Close
120       End With
130       Set rs = Nothing
140       If Len(strConcat) > 0 Then
150     strConcat = Left(strConcat, _
        Len(strConcat) - Len(pstrDelim))
160       End If
170       Concatenate = strConcat
Concatenate_Exit:
180   Exit Function
Concatenate_Error:
190   Call ErrorLog(ERL, Err.Number, Err.Description, "Concatenate", _
                  "modConcatenate")
200   Resume Concatenate_Exit
End Function

cn is another function which creates the connection.... I don't get the error on the cn module, but when it goes back to the concatenate function after running the cn function I get an error on line 20 which says "No value given for one or more required parameters." Error Number "-2147217904"

Here is the sql (That I have so far) for the query I am trying to create:

SELECT Format([ErrorNoteDate],"mm/dd/yyyy") AS NoteDate, tblErrorNotes.fkNCRIssueID, Concatenate("SELECT ErrorNote FROM tblErrorNotes WHERE pkNCRIssueID =" & [fkNCRIssueID]) AS ErrorDescription
FROM tblErrorNotes
GROUP BY Format([ErrorNoteDate],"mm/dd/yyyy"), tblErrorNotes.fkNCRIssueID, Concatenate("SELECT ErrorNote FROM tblErrorNotes WHERE pkNCRIssueID =" & [fkNCRIssueID])
HAVING (((tblErrorNotes.fkNCRIssueID)=[Forms]![frmErrorNotesProductType]![txtNCRIssueIDReference]));

I intend to the concatenate the date with the concatenated error notes in the query. But I can't even get the concatenation of the error notes to work.

Any ideas or suggestions would be helpful (especially if there is an easier way to achieve my goal. As well as anything as to where I am going wrong. I hope I have given enough info (as well as not too much. )

 

[boblarson]

Why not modify the function:

Function Concatenate(pstrSQL As String, Optional pstrDelim As String = ". ", _
                     Optional pstrLastDelim As String = ".", [B][COLOR=red]Optional IncludeDate As Boolean[/COLOR][/B]) _
                     As Variant
 
10    On Error GoTo Concatenate_Error
 
Dim rs As New ADODB.Recordset
20        rs.Open pstrSQL, cn
    Dim strConcat As String 'build return string
30        With rs
40      If Not .EOF Then
50          .MoveFirst
60          Do While Not .EOF
70              strConcat = strConcat & _
                .Fields(0) & pstrDelim
80              .MoveNext
90          Loop
100     End If
110     .Close
120       End With
130       Set rs = Nothing
140       If Len(strConcat) > 0 Then
150     strConcat = Left(strConcat, _
        Len(strConcat) - Len(pstrDelim))
160       End If
[B][COLOR=red]165       If IncludeDate Then[/COLOR][/B]
[B][COLOR=red]166            strConcat = strConcat & ", " & Date[/COLOR][/B]
[B][COLOR=red]169       End If
[/COLOR][/B]170       Concatenate = strConcat
Concatenate_Exit:
180   Exit Function
Concatenate_Error:
190   Call ErrorLog(ERL, Err.Number, Err.Description, "Concatenate", _
                  "modConcatenate")
200   Resume Concatenate_Exit
End Function

 

[Kryst51]

I tried what you said, but I still can't get the function to go past line 20 without erroring. So I can't even test your suggestion.

 

[boblarson]

It may be that it has to do with you trying to include it in a group by. What happens if you use it in a query without grouping? If it works then, just create the first query with that info and THEN a new query using that and group it like you want.

 

[Kryst51]

OMG, Thanks for all of your help Bob...... It was a stupid mistake on my part where I was asking it to match the wrong fields....

It should be this:

ErrorDescription: Concatenate("SELECT ErrorNote FROM tblErrorNotes WHERE fkNCRIssueID =" & [pkNCRIssueID])

instead of the other way around

"pkNCRIssueID =" [fkNCRIssueID])"

 

[boblarson]

Ah, I hate it when that happens (and I've been there, believe me).