Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    Forbes's Avatar
    Forbes is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Mar 2017
    Posts
    133

    Classic Allen B ConcatRelated()

    I am curious. I love this code and I use it and modify it often. Right now it shows the concatrelated() data as a list of names separated by line breaks.

    My question is, Is there a way to modify this so that the same results could show as such?



    Instead of:

    Bob
    Bill
    Bob

    I want:
    Bobx2
    Bill
    Code:
    Public Function ConcatRelated(strField As String, _
        strTable As String, _
        Optional strWhere As String, _
        Optional strOrderBy As String, _
        Optional strSeparator = vbCrLf) 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
        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

  2. #2
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    A Duplicates query will produce
    Bob 2
    You can use that to make Bobx2

  3. #3
    Forbes's Avatar
    Forbes is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Mar 2017
    Posts
    133
    Well if I don't have to modify the Query it would be nice, the concatrelated() field looks similar to this :
    Code:
    =ConcatRelated("[condname]","reports","[cond] = '" & [cond] & "' And ([condemp]='Brian Dill' Or [condemp]='Chris Dongle' Or [condemp]='James Polhurt') 
    AND timestamp Between #" & [Forms]![frmname]![txtStart] & "# And #" & [Forms]![frmname]![txtEnd] & "#")
    I would like to just be able to modify either the code in my initial post or this code in each report to achieve the desired effect:

    Brian Dill x2
    Chris Dongle x4
    James Polhurt x1

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    In response to your PM, I'd head in the direction aytee suggested, probably with a separate function so you could go either way. Or you could get really tricky and add an argument to this one. The finished SQL would look like

    SELECT FieldName, Count(*) AS HowMany
    FROM TableName
    WHERE...
    GROUP BY FieldName

    The function would be tweaked to not only get the name but add the count after it, concatenating them so you get your "Bob x2".
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    Forbes's Avatar
    Forbes is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Mar 2017
    Posts
    133
    SELECT condname, Count(*) AS HowMany
    GROUP_CONCAT(DISTINCT condname
    ORDER BY condname ASC SEPARATOR vbCrLf)
    FROM reports
    WHERE [condemp]='Brian Dill' Or [condemp]='Chris Dongle' Or [condemp]='James polhurt'
    GROUP BY cond;

    So probably like this then? I am going to play with this a little if you can see any errors let me know, thanks to both of you.
    Last edited by Forbes; 04-21-2017 at 12:16 PM. Reason: Spelling

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    No, I meant the SQL within the function would look like that. The SQL calling it wouldn't change, other than the function name.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    Forbes's Avatar
    Forbes is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Mar 2017
    Posts
    133
    Okay so where would I add the Count(*) AS HowMany line, I tried immedietly after select but I get an reserved word error
    Code:
    strSQL = "SELECT DISTINCT " & strField & " FROM " & strTable    
        If strWhere <> vbNullString Then
            strSQL = strSQL & " WHERE " & strWhere
        End If
        If strOrderBy <> vbNullString Then
            strSQL = strSQL & " ORDER BY ASC" & 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)

  8. #8
    Forbes's Avatar
    Forbes is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Mar 2017
    Posts
    133
    Okay this is where I am sitting, I put them Ascending and changed the function to Distinct Select so the names only show once, but now matter how I keep trying I can't seem to get Count(*) to work as well.

    Code:
    Option Compare Database
    
    Public Function ConcatRelated(strField As String, _
        strTable As String, _
        Optional strWhere As String, _
        Optional strOrderBy As String, _
        Optional strSeparator = vbCrLf) As Variant            'Seperater default  = Line Break = vbCrLf
    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 DISTINCT" & strField & " FROM " & strTable           'Select
        If strWhere <> vbNullString Then
            strSQL = strSQL & " WHERE " & strWhere                  'Where
        End If
        If strOrderBy <> vbNullString Then
            strSQL = strSQL & " ORDER BY ASC" & strOrderBy          'Order By
        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

  9. #9
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    The end result of the SQL should look like post 4. Allen's code is fairly complicated, in order to make it very flexible. You might find it simpler to write a function for this specific need, rather than try to maintain the flexibility of Allen's. If you post your db and I get bored, I'll take a shot at it.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  10. #10
    Forbes's Avatar
    Forbes is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Mar 2017
    Posts
    133
    Thank you for the information, I am going to try and give this one a shot on my own today and if I can't get it to run then I will let you know!

  11. #11
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    No problem, let me know how it goes.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  12. #12
    Forbes's Avatar
    Forbes is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Mar 2017
    Posts
    133
    Apologies we have been very busy, I will be able to upload this tonight. I gave it quite a few tries but was unable

  13. #13
    Forbes's Avatar
    Forbes is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Mar 2017
    Posts
    133
    Sent DB via PM, solution will be posted and explained.

  14. #14
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Which report? Any particular steps to test properly?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  15. #15
    Forbes's Avatar
    Forbes is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Mar 2017
    Posts
    133
    Every report would have to have that format, I think I had them set up to be listed as column already in that db so yeah just want to remove duplicate names on the report and replace that with the multiplier x2" Printing them from the shipping print report form by date and the closing print form

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Creating a Classic Login Screen
    By Xipooo in forum Tutorials
    Replies: 1
    Last Post: 02-03-2016, 07:02 AM
  2. ConcatRelated Help
    By aog928 in forum Programming
    Replies: 4
    Last Post: 09-27-2014, 10:39 AM
  3. Classic Log In Form
    By Derrick T. Davidson in forum Programming
    Replies: 7
    Last Post: 07-18-2014, 07:57 PM
  4. Replies: 2
    Last Post: 07-12-2013, 06:55 AM
  5. Replies: 1
    Last Post: 10-22-2012, 07:04 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums