Page 2 of 2 FirstFirst 12
Results 16 to 23 of 23
  1. #16
    jwhite is offline Competent Performer
    Windows 10 Access 2013 32bit
    Join Date
    Dec 2012
    Location
    North Carolina
    Posts
    349
    Any particular reason the DB is being sent via PM instead of posting within the thread? The thread cannot be followed or be useful to others without the DB.

  2. #17
    Forbes's Avatar
    Forbes is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Mar 2017
    Posts
    133
    Apologies for the db being sent via PM it was necessary, I will be posting a complete solution post with details once the fix is complete.

  3. #18
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    It would be easiest for me if you give me a report and a process to open it with the appropriate criteria.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  4. #19
    Forbes's Avatar
    Forbes is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Mar 2017
    Posts
    133
    Shipping report between 4/1 n 5/10 as long as there are duplicate names under 1 condition you should get your result on the report if it works

  5. #20
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Try this, Allen's code slightly tweaked. Changes in red, if I remembered them all:

    Code:
    Public Function ConcatRelatedCount(strField As String, _    strTable As String, _
        Optional strWhere As String, _
        Optional strOrderBy As String, _
        Optional strSeparator = vbCrLf) As Variant            'Seperater default tff = 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
        ConcatRelatedCount = Null
        
        'Build SQL string, and get the records.
        strSQL = "SELECT" & strField & ", Count(*) As HowMany FROM " & strTable     ' + Count(*) As "Select"
        If strWhere <> vbNullString Then
            strSQL = strSQL & " WHERE " & strWhere                  'Where
        End If
        strSQL = strSQL & "GROUP BY " & strField
        If strOrderBy <> vbNullString Then
            strSQL = strSQL & " ORDER BY" & 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) & " " & rs(1) & "x" & strSeparator
            End If
            rs.MoveNext
        Loop
        rs.Close
        
        'Return the string without the trailing separator.
        lngLen = Len(strOut) - Len(strSeparator)
        If lngLen > 0 Then
            ConcatRelatedCount = 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, "ConcatRelatedCount()"
        Resume Exit_Handler
    End Function
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  6. #21
    Forbes's Avatar
    Forbes is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Mar 2017
    Posts
    133
    This worked beautifully, Please advise in the code posted above I had to swap rs(1) and "x" to create the x2 vs 2x, Tonight I will post an explanation of the database what it does and what this problem solved using code examples and pictures from the database.

    Thank you pbaldy, I am grateful beyond words. Apologies for those attempting to follow this thread and await a solution I will do my best tonight to make what happened a learning experience.

    Please check back later tonight for the details, thanks again!

  7. #22
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Happy to help!
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  8. #23
    Forbes's Avatar
    Forbes is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Mar 2017
    Posts
    133
    Okay so the DB was created to report employees for things they missed by conditioning them in a drop down list style form, very simple interface on that end and essentially all it is used for by employees. This allows management to print reports to see where work is needed through out the process.

    The Problem: The reports would show 3 colums:

    Condition type--------------# of times condition was reported-----------------Names of conditioned

    The last column "Names of conditioned" is a ConcatRelated() formula that creates a list of all employees within a time period that were reported for that condition.

    unfortunately it would list as such:

    Bob, Bill, Ted, Bob, Mary

    as you can see this is not easy to read and employees that were reported more than once showed up more than once and out of order so first we changed the equation to columned like such:

    Bob
    Bill
    Ted
    Bob
    Mary

    This worked but we still had the issue of multiple names out of order which in turn makes the report harder to read.

    The solution was to make them appear with a multiplier next to the name as such

    Bob x2
    Ted x1
    Bill x1
    Mary x1

    This was accomplished by adding the count(*) to the concat function as such:
    Code:
    Public Function ConcatRelated(strField As String, _
        strTable As String, _
        Optional strWhere As String, _
        Optional strOrderBy As String, _
        Optional strSeparator = vbCrLf) As Variant            'Seperater default tff = 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" & strField & ", Count(*) As HowMany FROM " & strTable     ' + Select
        If strWhere <> vbNullString Then
            strSQL = strSQL & " WHERE " & strWhere                  'Where
        End If
        strSQL = strSQL & "GROUP BY" & strField
        If strOrderBy <> vbNullString Then
            strSQL = strSQL & " ORDER BY" & 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) & " " & "x" & rs(1) & 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
    The reports now show as desired and explained above thanks to Pbaldy's help and knowledge of Functions. I am going to mark this as solved, if you feel I have left anything out please ask and I will respond.

    Thank you everyone for your help and patience.

Page 2 of 2 FirstFirst 12
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