Microsoft KB Archive/141624

= ACC: How to Concatenate a List of Items from a Many-Side Table =

Article ID: 141624

Article Last Modified on 1/19/2007

-

APPLIES TO


 * Microsoft Access 2.0 Standard Edition
 * Microsoft Access 95 Standard Edition
 * Microsoft Access 97 Standard Edition

-



This article was previously published under Q141624



SUMMARY
Moderate: Requires basic macro, coding, and interoperability skills.

This article shows you a technique that you can use to concatenate information from multiple records based on the "many" side of a relationship and to format the results into a single line separated by commas on a report.

This article assumes that you are familiar with Visual Basic for Applications and with creating Microsoft Access applications using the programming tools provided with Microsoft Access. For more information about Visual Basic for Applications, please refer to your version of the "Building Applications with Microsoft Access" manual.

NOTE: Visual Basic for Applications is called Access Basic in Microsoft Access version 2.0. For more information about Access Basic, please refer to the "Building Applications" manual.



MORE INFORMATION
CAUTION: Following the steps in this example will modify the sample database Northwind.mdb (or NWIND.MDB in version 2.0). You may want to back up the Northwind.mdb (or NWIND.MDB) file and perform these steps on a copy of the database.   Create the following query as the basis for the sample report:      Query: qryCategoriesProducts -     Type: Select Query Join: Categories.[CategoryID] <-> Products.[CategoryID] (or Categories.[Category ID] <-> Products.[Category ID]           in Microsoft Access 2.0) Field: CategoryName (or Category Name in Microsoft Access 2.0) Table: Categories Sort: Ascending Field: ProductName (or Product Name in Microsoft Access 2.0) Table: Products Sort: None   Create the following report:      Report: rptCategoriesProducts ---     Caption: Products by Category RecordSource: qryCategoriesProducts   Create the following Sorting and Grouping sections in the report:      Field Expression: CategoryName Sort: Ascending Group Header: Yes Group Footer: Yes

Field Expression: ProductName Sort: Ascending Group Header: No        Group Footer: No                     Name the CategoryName header section grpHeaderCategoryID and set the header section's Height property to zero (0). Place the ProductName field in the report's detail section and set the detail section's Visible property to No.</li> Place the CategoryName field in the report's Category Name footer section.</li> Add an unbound text box named AllProducts to the report's Category Name footer section.

NOTE: You may want to set the CanGrow property of the AllProducts field to Yes if you anticipate that the number of items may exceed the width of the text box. This enables the text box to grow vertically.

NOTE: Place the CategoryName and AllProducts fields side by side for clarity when you print the report.</li>  Add the following lines to the report's Declarations section if they are not already there: Option Compare Database ' Use database order for string comparisons. Option Explicit Dim FirstPass As Integer </li>  Add the following code to the CategoryName header section's OnFormat property [Event Procedure]:

NOTE: In the following sample code, an underscore (_) at the end of a line is used as a line-continuation character. Remove the underscore from the end of the line when re-creating this code in Access Basic. Sub grpHeaderCategoryID_Format (Cancel As Integer, _                                     FormatCount As Integer) Me!AllProducts = Null FirstPass = False End Sub </li>  Add the following code to the detail section's OnFormat property [Event procedure]: Sub Detail1_Format (Cancel As Integer, FormatCount As Integer) On Local Error GoTo Detail1_Format_Err If Not FirstPass Then Me!AllProducts = Me![ProductName] FirstPass = True Else Me!AllProducts = Me!AllProducts & ", " & Me![ProductName] End If     Detail1_Format_End: Exit Sub Detail1_Format_Err: MsgBox Error$ Resume Detail1_Format_End End Sub </li> Close and save the report.</li>  Open the report in Print Preview. Note that the following data is displayed in vertical format. <pre class="fixed_text">Category: Beverages  Products:  Chai, Chang, Chartreuse verte, Cote de Blaye, Guarana Fantastica, Ipoh Coffee, Lakkalikoori, Laughing Lumberjack Lager, Outback Lager, Rhonbrau Klosterbier, Sasquatch Ale, Steeleye Stout

Category: Condiments  Products: Aniseed Syrup, Chef Anton's Cajun Seasoning, Chef Anton's Gumbo Mix, Genen Shouyu, Grandma's                                       Boysenberry Spread, Gula Malacca, Louisiana Fiery Hot Pepper Sauce, Louisiana Hot Spiced Okra, Northwoods Cranberry Sauce, Original Frankfurter grune Sobe, Sirop d'erable, Vegie-spread </li></ol>

Additional query words: horizontal

Keywords: kbhowto kbprogramming kbusage KB141624

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.