Complex VBA Concat Related Fields

[Pianopizza5]

Hello,



I want to concat a "defendant" field horizontally in a text box within a form when they have the same "item description" and "date delivered." (I have tried the Allen Browne ConcatRelated() function, but wasn't having any luck with that. I found a looping VBA code that should works, but I am having trouble completing it.)

The data in the query looks like this:

Item Description	Date Delivered	Defendants
Book		Smith, Joe
Book	1/1/2024	Blith, Ian
Book	1/1/2024	Hull, John
Book	2/1/2024	Kart, Ryan
Book	2/1/2024	Pon, Mary
Book	2/1/2024	Gull, Megan
Pencil		Blith, Ian
Pencil		Hull, John
Pencil		Smith, Joe
Pencil	3/1/2024	Kart, Ryan
Pencil	3/1/2024	Pon, Mary
Pencil	3/1/2024	Gull, Megan

... and I want the end result of:

Item Description	Date Delivered	Defendants
Book		Smith, Joe
Book	1/1/2024	Blith, Ian; Hull, John
Book	2/1/2024	Gull, Megan; Kart, Ryan; Pon, Mary
Pencil		Blith, Ian; Hull, John; Smith, Joe
Pencil	3/1/2024	Gull, Megan; Kart, Ryan; Pon, Mary

When I use the query to populate the continuous form, it creates a row for every combination of item descriptions and date delivered.

Item Description	Date Delivered
Book	2/1/2024
Book	1/1/2024
Book
Pencil	3/1/2024
Pencil

I was able to put together a VBA code that concatenates the defendants horizontally; however, all of the defendants appear in every row.

Dim DB As DAO.DatabaseDim RS As DAO.Recordset


Dim strSQL As String
Dim strText As String




strSQL = "SELECT qryDefendantTracking.FullName " _
    & "FROM qryDefendantTracking;""


Set DB = CurrentDb


    Set RS = DB.OpenRecordset(strSQL, dbOpenForwardOnly)
    Do Until RS.EOF
            If strText = "" Then
                strText = RS!FullName
                Else
                strText = strText & "; " & RS!FullName
            End If
        RS.MoveNext
    Loop


    RS.Close
    Set RS = Nothing
    Set DB = Nothing


Let Forms!frmCaseInformation!sbfrmDiscoveryLogItems!txtDefendants = strText


Debug.Print txtDefendants

(I will have an event that runs this when the form is opened.)

Please see the results below. (and they actually appear twice because in order to relate the defendants to the "item description" and "date delivered," I based the code on the query; and there are the defendants are linked to two items).

Item Description	Date Delivered	Defendants
Book	2/1/2024	Blith, Ian; Blith, Ian; Gull, Megan; Gull, Megan;… etc.
Book	1/1/2024	Blith, Ian; Blith, Ian; Gull, Megan; Gull, Megan;… etc.
Book		Blith, Ian; Blith, Ian; Gull, Megan; Gull, Megan;… etc.
Pencil	3/1/2024	Blith, Ian; Blith, Ian; Gull, Megan; Gull, Megan;… etc.
Pencil		Blith, Ian; Blith, Ian; Gull, Megan; Gull, Megan;… etc.

Does anyone have any ideas? I found this on YouTube and there was an additional video that went over how to handle this, but I was having trouble applying it to my database.

Thank you very much.

[Welshgasman]

I have use AB's concat code in the past without any issues.

Try this one then.
https://www.accessmvp.com/thedbguy/c...itle=simplecsv

[June7]

What does "wasn't having any luck with that" mean - error message, wrong result, nothing happens? Allen's code should be able to accommodate your requirement.

There is no criteria in your SQL so of course every record will return the same result.

Need a function that receives input as arguments (see AB or thedbGuy code).

I doubt calling function from an event procedure will be satisfactory. Call function in query or textbox.

Be aware this kind of procedure can cause slow performance in query/form with large dataset.

What is the SQL used for form RecordSource? Possibly presentation in a form is not best option, maybe should be a report.

Provide sample of raw data tables. Build tables in post or attach file (db or Excel).

I did a test using the first dataset in OP as source for form and function. Called function in textbox with:

=ConcatRelated("Defendants","Data","ItemDescription & Format(DateDelivered,'yyyymmdd')='" & [ItemDescription] & Format([DateDelivered],"yyyymmdd") & "'","","; ")

(and if you want output to be alphabetic order, use "Defendants" in function ORDER BY argument)

In query:

SELECT Data.ItemDescription, Data.DateDelivered, Data.Defendants, 
ConcatRelated("Defendants","Data","ItemDescription & Format(DateDelivered,'yyyymmdd')='" & [ItemDescription] & Format([DateDelivered],"yyyymmdd") & "'","Defendants","; ") AS Defs
FROM Data;

ItemDescription	DateDelivered	Defendants	Defs
Book		Smith, Joe	Smith, Joe
Book	1/1/2024	Blith, Ian	Blith, Ian; Hull, John
Book	1/1/2024	Hull, John	Blith, Ian; Hull, John
Book	2/1/2024	Kart, Ryan	Gull, Megan; Kart, Ryan; Pon, Mary
Book	2/1/2024	Pon, Mary	Gull, Megan; Kart, Ryan; Pon, Mary
Book	2/1/2024	Gull, Megan	Gull, Megan; Kart, Ryan; Pon, Mary
Pencil		Blith, Ian	Blith, Ian; Hull, John; Smith, Joe
Pencil		Hull, John	Blith, Ian; Hull, John; Smith, Joe
Pencil		Smith, Joe	Blith, Ian; Hull, John; Smith, Joe
Pencil	3/1/2024	Kart, Ryan	Gull, Megan; Kart, Ryan; Pon, Mary
Pencil	3/1/2024	Pon, Mary	Gull, Megan; Kart, Ryan; Pon, Mary
Pencil	3/1/2024	Gull, Megan	Gull, Megan; Kart, Ryan; Pon, Mary

[Pianopizza5]

Thanks all for your input.

I pulled the Allen Browne code and have been trying to fill it in. I am not sure how to set the WHERE clause, as it is based on the row in the form (Form!frmInformation!ItemDescription)?

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 qryDefendantTracking.FullName FROM qryDefendantTracking"
    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

I did a Debug Compile on it and everything seemed ok.

How do I call the Function in the Module? I read that you set it as the Control Source in the Data section of the Property Sheet (such as =ConcatRelate(field to be displayed, where the data comes from). I kept getting the #Name? error.

[Pianopizza5]

Thanks, I tried the code quickly I've been able to make something similar to that work, but it also has to meet the criteria.

Giving the AB code a shot again. Not sure how to put the code in the WHERE clause.

[June7]

See my previous post, possibly you saw before I edited it.

[Pianopizza5]

Thank you so much for your response. I apologize, I didn't see this.

I modified your code to fit my database. The fields are "FullName", "DescriptionOfDocsSent", and "DateDelivered" and the RecordSource is "qryAllTracking." (not positive if I can call from a query... so this might be part of the problem.)

Public Function ConcatDef()

Dim SQL As String


SQL = "SELECT qryAllTracking.DescriptionOfDocsSent, qryAllTracking.DateDelivered, qryAllTracking.FullName, " _
    & "ConcatRelated("FullName", "qryAllTracking", "DescriptionOfDocsSent & Format(DateDelivered,'yyyymmdd')='" & [DescriptionOfDocsSent] & Format([DateDelivered], "yyyymmdd") & "'", "FullName", "; ") As Defendants " _
    & "FROM qryAllTracking;"
    
End Function

When I compile it I get the error "Compile error: Syntax error." It seems to be at the "ConcatRelated("FullName", but I can't tell what is wrong with this.



Any thoughts?

[CJ_London]

Any thoughts?

you can't mix single and double quotes the way you have

Start with some sql that works, then keep adding until it fails - at which point you should see where the problem is

[Welshgasman]

Do you really have a field called DescriptionOfDocsSent20241214 ?

Put the criteria into a string variable and debug.print that until you get it correct, then use that in the function.

[June7]

The SQL I posted works in query object - building in VBA is going to be tricky because of embedded quotes and apostrophe delimiters in the function call.
Formatting used in case date field has time component, if it doesn't then formatting probably overkill.

Why are you building it in VBA?
If you really must build in VBA, consider:

strSQL = "SELECT qryAllTracking.*, " & _
        "ConcatRelated(""FullName"", ""qryAllTracking"", ""DescriptionOfDocsSent & DateDelivered='"" & [DescriptionOfDocsSent] & [DateDelivered] & ""'"", ""FullName"", ""; "") As Defs " & _
        "FROM qryAllTracking;"

[Pianopizza5]

Thank you all so much. I inserted my table names into the SQL code and was getting a Undefined function error. This could be an error in my code or perhaps the function isn't supported (I am using Microsoft Access 365).



That is part of the reason why I tried using the loop function in VBA (and a lot of the examples I found online where in VBA). Completely fine with performing the concate function in SQL.

My original SQL formula works (without the ConcatRelate).

SELECT tblDiscoveryProduction.DescriptionOfDocsSent, qryDefendantTracking.DateDelivered, qryDefendantTracking.FullName
FROM qryDefendantTracking RIGHT JOIN tblDiscoveryProduction ON qryDefendantTracking.tblDiscoveryTracking.Discover yID = tblDiscoveryProduction.DiscoveryID;

Then, I did my best to fill in the code in the earlier post. However, I was getting the Function Undefined error.

SELECT tblDiscoveryProduction.DescriptionOfDocsSent, qryDefendantTracking.DateDelivered, qryDefendantTracking.FullName,
ConcatRelated("qryDefendantTracking.FullName", "tblDiscoveryProduction.DescriptionOfDocsSent & Format(qryDefendantTracking.DateDelivered ,'yyyymmdd')=" & [tblDiscoveryProduction.DescriptionOfDocsSent] & Format([qryDefendantTracking.DateDelivered],"yyyymmdd") & "'","qryDefendantTracking.FullName","; ") AS Defs
FROM qryDefendantTracking RIGHT JOIN tblDiscoveryProduction ON qryDefendantTracking.tblDiscoveryTracking.Discover yID = tblDiscoveryProduction.DiscoveryID;

As a way to troubleshoot, I took out the Format part of the code, but had the same results.

SELECT tblDiscoveryProduction.DescriptionOfDocsSent, qryDefendantTracking.DateDelivered, qryDefendantTracking.FullName,
ConcatRelated("qryDefendantTracking.FullName", "tblDiscoveryProduction.DescriptionOfDocsSent & qryDefendantTracking.DateDelivered = '" & [tblDiscoveryProduction.DescriptionOfDocsSent] & [qryDefendantTracking.DateDelivered] & "'", "; ") AS Defs
FROM qryDefendantTracking RIGHT JOIN tblDiscoveryProduction ON qryDefendantTracking.tblDiscoveryTracking.Discover yID = tblDiscoveryProduction.DiscoveryID;

So, to the best of my abilities, I tried to solve it logically, but no cigar.

SELECT tblDiscoveryProduction.DescriptionOfDocsSent, qryDefendantTracking.DateDelivered, qryDefendantTracking.FullName,
ConcatRelated(qryDefendantTracking.FullName, tblDiscoveryProduction.DescriptionOfDocsSent & qryDefendantTracking.DateDelivered = tblDiscoveryProduction.DescriptionOfDocsSent & qryDefendantTracking.DateDelivered) AS Defs
FROM qryDefendantTracking RIGHT JOIN tblDiscoveryProduction ON qryDefendantTracking.tblDiscoveryTracking.Discover yID = tblDiscoveryProduction.DiscoveryID;

Am I typing something in wrong?

[Gicu]

Did you add Allan's function (ConcatRelated) to a public module and declared it as a public function?

[Pianopizza5]

Hello,

Thank you for your comment. Yes, I tried doing that but wasn't sure where to put the WHERE clause (see a post above). Then, it was suggested that I write the formula in SQL, which is where I'm at now.

I apologize all for taking so much time on this. I've never done this before.

[Gicu]

Please show us the pertinent tables (would be better if you could upload a sample db with just those tables and one or two dummy records). You must have a standard module in your db where you have Allen's function, that is the reason for your Undefined function error message.
Once you do you start with the query you say it works:
SELECT tblDiscoveryProduction.DescriptionOfDocsSent, qryDefendantTracking.DateDelivered, qryDefendantTracking.FullName
FROM qryDefendantTracking RIGHT JOIN tblDiscoveryProduction ON qryDefendantTracking.tblDiscoveryTracking.Discover yID = tblDiscoveryProduction.DiscoveryID;

and you add the concatenated field.

Nowadays I use dbGuy's SimpleCSV() function (already suggested to you in post #2) as I find it a bit easier to implement. You will also need to copy that function into a standard module.

Cheers,

[Pianopizza5]

ConcatTest.accdb

Thank you. Here is a sample database of customers and their orders.

The basic query works (as illustrated in qryOrdersExample); but when I add the ConcatRelated formula, I am getting the same error (in qryConcatOrders). I checked and I do not have any Modules.

What I want the end result to be is:

Item	DateOrdered	FullName
Apple	12/13/24	Fred, Mary
Apple	12/9/24	Fred
Banana	12/9/24	Fred, John

This is just an example, so it may not make the most sense information wise, but it illustrates what I am trying to achieve.