Results 1 to 15 of 15
  1. #1
    Old Fat Dog is offline Novice
    Windows 10 Office 365
    Join Date
    May 2020
    Posts
    9

    How to display multiple values from a single field "in line" instead of as a sort/group section?

    I'm new to Access (and this forum), so I'm hoping this is a simple problem to solve



    We are a manufacturer that works with numerous Parent Companies.

    Each Parent Company operates under a unique DBA name for us.

    Under that DBA Name, each Parent Company operates one or more different Locations.

    I'm trying to create a contact directory report that shows one instance of the DBA Name/Parent Company contact info, and then a list of the locations they operate.

    Here's the current design view of my report:

    Click image for larger version. 

Name:	design view.jpg 
Views:	14 
Size:	222.0 KB 
ID:	41850

    Here's what I'm trying to get it to look like. Current view on top, desired view beneath that:

    Click image for larger version. 

Name:	report view.jpg 
Views:	15 
Size:	139.5 KB 
ID:	41851

    Any suggestions? If I need to upload more info or images, let me know!

  2. #2
    Old Fat Dog is offline Novice
    Windows 10 Office 365
    Join Date
    May 2020
    Posts
    9
    I don't know if this will help, but here are the relationships.

    Click image for larger version. 

Name:	relationships.jpg 
Views:	15 
Size:	73.3 KB 
ID:	41852

  3. #3
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    21,285
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  4. #4
    Old Fat Dog is offline Novice
    Windows 10 Office 365
    Join Date
    May 2020
    Posts
    9
    [SIZE=2]
    Quote Originally Posted by pbaldy View Post
    Thank you! I don't really understand much of that, though. I'm taking it on faith by copying and pasting without really knowing why or what I'm doing. (Sort of the story of my life, actually....)

    I copied and compiled the VBA code from that page as instructed.

    I then went into the design of the form and added a new text box as a test.

    But -- I don't know what to put in the Control Source field in the text box properties.

    The link you posted had one example:

    Code:
    =ConcatRelated("OrderDate", "tblOrders", "CompanyID = " & [CompanyID])
    How do I customize what's in between the parentheses to match my field name [Location_Name] and also preferred layout (stacked rather than comma separated)?

    In my case, [Location_Name] is what I want to concatenate, and nothing else.

  5. #5
    Old Fat Dog is offline Novice
    Windows 10 Office 365
    Join Date
    May 2020
    Posts
    9
    Note that I'd prefer to stack the names, but I can live with comma separated values when displayed.

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    21,285
    Try

    =ConcatRelated("Location_Name", "Locations", "[DBA Name]= '" & [DBA Name] & "'")

    using the actual object names. If that works, I think I've been able to change the delimiter to

    vbCrLf

    or maybe

    Chr(13) & Chr(10)

    but I'd have to experiment.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    Old Fat Dog is offline Novice
    Windows 10 Office 365
    Join Date
    May 2020
    Posts
    9
    I got ConcatRelated to work, but as you can see, Access is still doubling up the entry as though it were grouping by location.

    Click image for larger version. 

Name:	design view v2.jpg 
Views:	15 
Size:	67.7 KB 
ID:	41854

    Click image for larger version. 

Name:	design view 02.jpg 
Views:	13 
Size:	113.1 KB 
ID:	41855

  8. #8
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    21,285
    That's coming from the source of the report. It should be a query that only returns one instance of each DBA name.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  9. #9
    Old Fat Dog is offline Novice
    Windows 10 Office 365
    Join Date
    May 2020
    Posts
    9
    Quote Originally Posted by pbaldy View Post
    That's coming from the source of the report. It should be a query that only returns one instance of each DBA name.
    Sorry to be such a noob, but I don't understand what you're saying. My apologies if my learning curve is steeper than you're used to! I imagine it's like a surgeon having to explain how to put on a band-aid

  10. #10
    Old Fat Dog is offline Novice
    Windows 10 Office 365
    Join Date
    May 2020
    Posts
    9
    Would it help if I uploaded the actual database (with only a few records of test data)? I can certainly do that.

  11. #11
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    21,285
    No worries, I'm talking about the record source property of the report. Yes, it would help to be able to play with the database.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  12. #12
    Old Fat Dog is offline Novice
    Windows 10 Office 365
    Join Date
    May 2020
    Posts
    9
    Quote Originally Posted by pbaldy View Post
    Yes, it would help to be able to play with the database.
    I really appreciate the advice. I've attached a copy of the database. It's all fake data, but all tables and the report are actual.

    I added:
    • 3 companies with 1 location
    • 1 company with 5 locations
    • 1 company with 2 locations


    Access Forum Sample DB.zip

  13. #13
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    21,285
    Try making these 3 changes:

    Change the report record source to (I dropped the locations table):

    SELECT [DBA Name].DBA_Name, [Parent Company].Parent_Company, [Parent Company].Parent_Street_Address, [Parent Company].Parent_City, [Parent Company].Parent_State, [Parent Company].Parent_Postal_Code, [Parent Company].Parent_Country, [Parent Company].Parent_Office_Phone, [Parent Company].Parent_Website FROM [DBA Name] INNER JOIN [Parent Company] ON [DBA Name].[DBA_Name] = [Parent Company].[DBA_Name];

    Run the report, see if it works like you want. If so, now we'll put on separate lines like you wanted. Change the textbox Can Grow property to yes and paste this function instead of what's there (my changes in red):

    Code:
    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
        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
                        strOut = strOut & rsMV(0) & vbCrLf
                    End If
                    rsMV.MoveNext
                Loop
                Set rsMV = Nothing
            ElseIf Not IsNull(rs(0)) Then
                'strOut = strOut & rs(0) & strSeparator
                strOut = strOut & rs(0) & vbCrLf
            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
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  14. #14
    Old Fat Dog is offline Novice
    Windows 10 Office 365
    Join Date
    May 2020
    Posts
    9
    Bingo! That worked. In the end, I had to change the layout of the fields to get it all to display properly, but I'm happy with the compromise.

    Here's how it looked when I kept the "Locations" list in its original place on the right side -- you can see it was pushing down the City/State/Zip field to accommodate the extra location names:

    Click image for larger version. 

Name:	report-design view 01.jpg 
Views:	9 
Size:	114.6 KB 
ID:	41884

    To work around that issue, I relocated the "Locations" field:

    Click image for larger version. 

Name:	report-design view 02.jpg 
Views:	9 
Size:	103.2 KB 
ID:	41885

    Thank you so much for your help!!
    Attached Thumbnails Attached Thumbnails report-design view 01.jpg  

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

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 7
    Last Post: 06-29-2019, 10:58 AM
  2. Replies: 7
    Last Post: 08-31-2018, 01:40 PM
  3. Query for multiple values to display "All"
    By vicsaccess in forum Queries
    Replies: 3
    Last Post: 06-25-2016, 08:54 PM
  4. Replies: 4
    Last Post: 12-20-2015, 12:16 PM
  5. Replies: 6
    Last Post: 08-11-2011, 10:41 AM

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 - Senior Forums