Concatenate Records to include Initials and Date and Comments

I found sample code from Allen Brown's Modifedby (Peter Matthews) website - Need help to modify it so that my results include additional fields.

I need to concatenate a group of comments into 1 display field, but each comment must include the date of comment and the user name(intiials)
What do I need to modify the code to include the necessary fields?

Data looks like
Date             User           Comment
3/14/14          KM            Go to the market
5/1/15            SJ               Went to the Market
5/13/15          SJ               Got bread

Should look like:
CityID 1
3/14/14:KM - Go to the market; 5/1/15:SJ - Went to the Market; 5/13/15:SJ - Got bread;
Per City ID 1
Function DConcat(ConcatColumns As String, Tbl As String, Optional Criteria As String = "", _
    Optional Delimiter1 As String = "; ", Optional Delimiter2 As String = "; ", _
    Optional Distinct As Boolean = True, Optional Sort As String = "", _
    Optional Limit As Long = 0)

    ' Function by Patrick G. Matthews, basically embellishing an approach seen in many
    ' incarnations over the years

    ' Requires reference to Microsoft DAO library

    ' This function is intended as a "domain aggregate" that concatenates (and delimits) the
    ' various values rather than the more usual Count, Sum, Min, Max, etc.  For example:
    '
    '    Select Field1, DConcat("Field2", "SomeTable", "[Field1] = '" & Field1 & "'") AS List
    '    FROM SomeTable
    '    GROUP BY Field1
    '
    ' will return the distinct values of Field1, along with a concatenated list of all the
    ' distinct Field2 values associated with each Field1 value.

    ' ConcatColumns is a comma-delimited list of columns to be concatenated (typically just
    '   one column, but the function accommodates multiple).  Place field names in square
    '   brackets if they do not meet the customary rules for naming DB objects
    ' Tbl is the table/query the data are pulled from.  Place table name in square brackets
    '   if they do not meet the customary rules for naming DB objects
    ' Criteria (optional) are the criteria to be applied in the grouping.  Be sure to use And
    '   or Or as needed to build the right logic, and to encase text values in single quotes
    '   and dates in #
    ' Delimiter1 (optional) is the delimiter used in the concatenation (default is ", ").
    '   Delimiter1 is applied to each row in the code query's result set
    ' Delimiter2 (optional) is the delimiter used in concatenating each column in the result
    '   set if ConcatColumns specifies more than one column (default is ", ")
    ' Distinct (optional) determines whether the distinct values are concatenated (True,
    '   default), or whether all values are concatenated (and thus may get repeated)
    ' Sort (optional) indicates whether the concatenated string is sorted, and if so, the
    '   columns used for the sort.  As you would in an ORDER BY clause, use Asc or Desc to
    '   indicate whether the column is sorted ascending or descending.  If Asc/Desc is
    '   omitted, Asc is assumed by the query engine
    ' Limit (optional) places a limit on how many items are placed into the concatenated string.
    '   The Limit argument works as a TOP N qualifier in the SELECT clause

    Dim rs As DAO.Recordset
    Dim SQL As String
    Dim ThisItem As String
    Dim FieldCounter As Long

    On Error GoTo ErrHandler

    ' Initialize to Null

    DConcat = Null

    ' Build up a query to grab the information needed for the concatenation

    SQL = "SELECT " & IIf(Distinct, "DISTINCT ", "") & _
            IIf(Limit > 0, "TOP " & Limit & " ", "") & _
            ConcatColumns & " " & _
        "FROM " & Tbl & " " & _
        IIf(Criteria <> "", "WHERE " & Criteria & " ", "") & _
        IIf(Sort <> "", "ORDER BY " & Sort, "")
      Debug.Print SQL
    ' Open the recordset and loop through it:
    ' 1) Concatenate each column in each row of the recordset
    ' 2) Concatenate the resulting concatenated rows in the function's return value

    Set rs = DBEngine(0)(0).OpenRecordset(SQL, dbOpenForwardOnly)
    With rs
        Do Until .EOF

            ' Initialize variable for this row

            ThisItem = ""

            ' Concatenate columns on this row

            For FieldCounter = 0 To rs.Fields.Count - 1
                ThisItem = ThisItem & Delimiter2 & Nz(rs.Fields(FieldCounter).value, "")
            Next

            ' Trim leading delimiter

            ThisItem = Mid(ThisItem, Len(Delimiter2) + 1)

            ' Concatenate row result to function return value

            DConcat = Nz(DConcat, "") & Delimiter1 & ThisItem
            .MoveNext
        Loop
        .Close
    End With

    ' Trim leading delimiter

    If Not IsNull(DConcat) Then DConcat = Mid(DConcat, Len(Delimiter1) + 1)

    GoTo Cleanup

ErrHandler:

    ' Error is most likely an invalid database object name, or bad syntax in the Criteria

    DConcat = CVErr(Err.Number)

Cleanup:
    Set rs = Nothing

End Function

Open in new window


This works as far as combining the Comments/Notes into 1 field, however, I still need to add in the Users name and Date of comments.  

Any suggestions is appreciated.
Karen SchaeferBI ANALYSTAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

rspahitzCommented:
So you're looking to move all the lines into a single line?  What about the "header" and "footer" in your example?  Do you need those?  Where do they come from?
rspahitzCommented:
Looking at the code, it seems to allow you to pass in some delimiters:
Function DConcat(ConcatColumns As String, Tbl As String, Optional Criteria As String = "", _
    Optional Delimiter1 As String = "; ", Optional Delimiter2 As String = "; ", _
    Optional Distinct As Boolean = True, Optional Sort As String = "", _
    Optional Limit As Long = 0)

It seems that whatever is calling this from your code is passing in a TAB (vbTab?) to Delimiter2 and maybe a new line (vbNewLine?) to Delimiter1.

To get what you want, you should probably use ";" for Delimiter 1 and ":" for Delimiter2.  However, to get the " - " before each description, you should probably include a third delimiter (maybe Delimiter3) then adjust your "For" loop.

1. Change the function definition"
Function DConcat(ConcatColumns As String, Tbl As String, Optional Criteria As String = "", _
    Optional Delimiter1 As String = "; ", Optional Delimiter2 As String = "; ", Optional Delimiter3 As String = "; ", _
    Optional Distinct As Boolean = True, Optional Sort As String = "", _
    Optional Limit As Long = 0)

Open in new window


2. Change the For loop at line 77
If FieldCounter = 0 Then
   ThisItem = ThisItem & Delimiter2 & Nz(rs.Fields(FieldCounter).value, "")
Else
   ThisItem = ThisItem & Delimiter3 & Nz(rs.Fields(FieldCounter).value, "")
End If

Open in new window


3. Update whatever code calls this function to include the 3rd parameter after the second one.  You may need to do this in multiple places.  Also, you may need to make this into a new function to allow backward compatibility if the function is used for other things.
Karen SchaeferBI ANALYSTAuthor Commented:
Thanks for the input, what is the proper syntax for implementation within a Query?

These changes still do not display date or initials.  and it is duplicating the messages.

"Confirmed tax rates, added validation links; Confirmed tax rates, added validation links; Updated links.  VAT in border zone plus tourist tax; Updated links.  VAT in border zone plus tourist tax"

Thanks,

K
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

rspahitzCommented:
I'm not quite sure of your question.
>Thanks for the input, what is the proper syntax for implementation within a Query?
What is your goal with this?  How are you calling the function now?
That may also help explain why you're not getting the expected output.

What is the output you were getting before you put in my code-change suggestions? (I thought it was the first group you indicated.)

--
So maybe right now you are running a query to generate your list (of 3 columns)
Are you looking to replace that with the single-line output (1 column)?
For that, it's probably best to just create a new unbound field and set the DataSource = to a new version of the function, something like this:

=DConcat2([Date].Value, [User].Value, [Comment].Value)

(The above assumes that your database table field names are Date, User and Comment---adjust as needed)

then have the function be something like this:

Function DConcat2(ADate, AUser, AComment) As String
   DConcat2 = ADate & ":" & AUser & " - " & AComment & ";"
End Function

That will merge the pieces into a single field, but not combine all records into a single "record"
So is that closer to what you want?
Karen SchaeferBI ANALYSTAuthor Commented:
Ok I almost got it to work,  I need to make sure the messages are in chronological order (desc), However, due to the need for the Distinct in the code, it prevents this from happening.  DO you know of any workarounds.

My solution so far includes changing where I concatenate the first half of the data.  I am using a Calculated field on the original table  to combine the Date, User, Comment into 1 string, Then I am using this concatenated field within the Code.  If we can get this to sort in descending order so the latest comment is always on top of the memo field, We will be successful.

Thanks  for your input.  Let me know if a sample db would be beneficial.

K
Karen SchaeferBI ANALYSTAuthor Commented:
Also Note I reverted back to Patrick's & Allen's original code, now that I have setup the concatenation within the table, it works great except for the date order issue.

Thanks,
K
rspahitzCommented:
>I am using a Calculated field on the original table  to combine the Date, User, Comment into 1 string, Then I am using this concatenated field within the Code.  If we can get this to sort in descending order

Can you simply add the Date field into the query and sort on it (but don't display it)?
Karen SchaeferBI ANALYSTAuthor Commented:
No
Can you simply add the Date field into the query and sort on it (but don't display it)?

Due to the distinct, unable to use Orderby.  I tried to remove the distinct but it breaks the code,  Just a thought do you think if I set the order by on a query before I used the query with the code could affect the order by?
Karen SchaeferBI ANALYSTAuthor Commented:
Ok that didn't make a difference, any other ideas.
Karen SchaeferBI ANALYSTAuthor Commented:
I found a solution - Code for ConcatRelated - it allowed me to set the orderby

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
    'Purpose:   Generate a concatenated string of related records.
    'Return:    String variant, or Null if no matches.
    'Arguments: strField = name of field to get results from and concatenate.
    '           strTable = name of a table or query.
    '           strWhere = WHERE clause to choose the right values.
    '           strOrderBy = ORDER BY clause, for sorting the values.
    '           strSeparator = characters to use between the concatenated values.
    'Notes:     1. Use square brackets around field/table names with spaces or odd characters.
    '           2. strField can be a Multi-valued field (A2007 and later), but strOrderBy cannot.
    '           3. Nulls are omitted, zero-length strings (ZLSs) are returned as ZLSs.
    '           4. Returning more than 255 characters to a recordset triggers this Access bug:
    '               http://allenbrowne.com/bug-16.html
    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
        strSQL = strSQL & " desc"
    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

Open in new window


However I am having a minor issue getting the subform to refresh after a new record has been entered into the note table.

Private Sub Form_AfterUpdate()
    Dim strSQL As String
    strSQL = "SELECT ConcatRelated('ConcatNotes','tblNotes_test',[CityRecid]=[Forms]![frmNotesTest]![CityRecID],[DateCreated]) AS Notes " & _
                " FROM tblNotes_test"
    Forms![frmNotesTest]![frmNotesMerged_sub].Form.RecordSource = strSQL
DoCmd.GoToControl "frmNotesMerged_sub"
Requery
End Sub

Open in new window


this is called from subform1 and I need to update Subform 2( frmNotesMerged_sub)

What am I missing?

K
Karen SchaeferBI ANALYSTAuthor Commented:
here is what my code looks like behind the subform 1

Option Compare Database
Option Explicit
Private Sub Form_AfterUpdate()
    Dim strSQL As String
    strSQL = "SELECT CityRecID, ConcatRelated('ConcatNotes','tblNotes_test',[CityRecid]=[Forms]![frmNotesTest]![CityRecID],[DateCreated]) AS Notes " & _
                " FROM tblNotes_test" & _
                " ORDER by DateCreated desc"
Debug.Print strSQL
    Forms![frmNotesTest]![frmNotesMerged_sub].Form.RecordSource = strSQL
 '   Forms![frmNotesTest]![frmNotesMerged_sub].Form.Requery
End Sub

Private Sub Form_Current()
    Me.Parent.Controls![frmNotesMerged_sub].Requery

End Sub
 

Open in new window


Problem is that the descending order gets lost when I requery.
rspahitzCommented:
The order-by in the SQL command will only apply "AfterUpdate" so it wouldn't apply on a requery.

At this point, I'm not sure where you stand on things.
At some point it may make sense to step back and re-evaluate the task if you hit too many obstacles. It may make sense to see if there is another solution.  Are you simply trying to create a report, or are you trying to create an export file, or something else?

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Karen SchaeferBI ANALYSTAuthor Commented:
thanks for all your input, Turns out I was making a mountain out of a mole hill, turns out this is not function that is not needed.

Thanks for your time.

K
rspahitzCommented:
Whew!  Glad you got it!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.