It often makes sense to show lists as comma-separated items, especially in reporting. For example:
available colours: red, green, blue.
However, the reporting engine is biased towards tabular layout:
The latter is easy to achieve, even using only the report wizard. The former requires some programming.
The other day, I needed to produce a comma-separated list on a report, and was about to download from another article, Access & VB's Missing Domain Lookup Functions
, the function DList(), designed for exactly this purpose. I was reluctant to add a module in this particular database, so I thought a little longer and realised that the report I had created already did 99% of the job.
Two lines of code are all that is needed to switch from the tablular layout to a list.
The tabular layout uses a header, for example a group header if several item lists are displayed, and a detail section. The footer would be used to display sub-totals or to provide some white space at the end of each group. The sections are printed in sequence: header, detail(s), footer, header, details(s), ... Each group will have at least one detail section. However, depending on the record source and the data, there might be no item to display in a given group. There will be a detail section, but the fields meant to display the item will be Null.
The idea is to use the formatting events of these sections to collect the items and create a list for the group footer. As promised, this requires only two lines of code. This adds an item to the list:
ItemList = ItemList+', ' & ItemName
The list needs to be reset for each group:
ItemList = Null
This is so simple that it can actually be done using macros and the SetValue action. (It can be an embedded macro since Access 2007, but SetValue is not available in Sandbox mode, so the database must be “trusted”.)
In the attached tiny demo, I have created two tables called MainTable and LinkedTable. The main table contains names, and the linked table a list of colours associated to each name, the foreign key is `MainID´ referrencing `ID´, there is an additional `ItemNb´ to create a two-field key and incidentally a sort order for the colours.
Using the function DFind() mentioned in the introduction, I could get a list of colours associated to ID=1 like this:
One solution would thus be to base the report on the main table only, and use this function to obtain the list of colours for each name. The last argument is the sort order; changing it to "ItemName" would produce an alphabetical list.
It is actually easier without the function. The report "Concatenation" is based on a query linking both tables, using a LEFT JOIN in order to show all records from the main table. A grouping is added, with both header and footer, on Main.ID. The detail section shows each ItemName, or Null if there are none to display. The sections have been renamed for readability; the text box collecting the list is called txtList, on the group footer.
Private Sub secDetail_Format(Cancel As Integer, FormatCount As Integer)
If FormatCount = 1 Then txtList = txtList + ", " & ItemName
Private Sub secMainHeader_Format(Cancel As Integer, FormatCount As Integer)
txtList = Null
The `detail format´ event handler checks the format count argument. This is in order to avoid adding the same item twice if the reporting engine needs to reformat the same section for some reason. This precaution isn't really necessary, as the detail section will not be printed at all in the end.
In order to avoid adding a comma to an empty list, the operator `+´ is used. When one of the expressions on either side or the operator is Null, the entire expression becomes Null. This way, the comma will be added only between items, or rather only once the list is no longer Null.
After the grouping, there is also a sorting instruction. The sorting on `ItemNb´ is supposedly the intended sort order for this data; if `ItemName´ is used instead, the list will be alphabetised.
The last step is to make the detail section invisible. It needs to to contain a control displaying ItemName, which will no longer be printed, because only fields actually used on the report are available to the Visual Basic module. Notice that the format event occurs for invisible sections. (The detail section has been left visible in the demo file, change it to see the final intended result.)
The database in in Access 2000 format, but was created in Access 2007. After opening the database in an earlier version, if the demo doesn't work, please remove any missing reference: from Visual Basic Editor, choose (Tools | References) and uncheck any libraries marked as missing.
Reports without a visible detail section seem like a strange idea at first. I have used them a lot in classes, because they provide a simple way to execute total queries without opening the query editor at all. The reporting engine will create them in the background, based on the selected grouping(s) and the aggregate functions used in the controls. In many cases, the details are not important, so the detail section is simply hidden or reduced to zero height.
It had never occured to me how simple it was to create a concatenated list using a similar principle, and I hope this trick will be useful to others.
Markus G Fischer
This article is part of the EE-book unrestricted Access