Now I’m not going to take credit for this next piece of Microsoft Access database VBA code as it was originally created by a well-known Allen Browne – many thanks!

Instead, I’m going to borrow his code for you (my regular readers) just in case you haven’t seen this before and add an element of quickly adding criteria to a query as a workaround to avoid expanding the VBA code itself.

There are potential problems and errors this code will throw at you if you try to add criteria in the normal manner or embed any SQL directly into the calling custom function which means using some common sense and logic to deal with the challenge here.

Microsoft Access Database: Concatenating Multiple Rows Into Single Line In MS Access

First of all, let’s get the function to work and the query understood. In a new module, you will need to add the custom function called ‘ConcatRelated‘ which can be downloaded from Allen Browne’s website article concatenate VBA function.

The query I will be using will need to look like the following:

This query shows only orders (dates in one field) for the year 2009 for each customer with a total summary order amount.

Because it’s a groups and total query summing the order amount, just trying to filter for 2009 by the order date field will not work as the function itself doesn’t have any provision for either an SQL statement for criteria or any code workflows to handle criteria.

One could amend the code but for simplicity and reuse (and to honour the great work from Mr Browne), we need to have a workaround solution.

The trick will be to create a separate query including the key fields and fields to link back to another table, any criteria fields plus any other calculated fields that will be used to forward on to the next query (also known as nested queries).

So I created a separate query called ‘qry_2009Orders‘ which contained the foreign key field (Customer ID), Company Name, Order Amount and order date which is where I added the filter (for 2009 only).

In a new query, I created a linked between the table: Customers and the new query (as shown above) and added the fields for the output required.

Along with Company Name and Order Amount which was grouped by Company Name and Sum on the Order Amount, I added an calculated expression in a new field (column) with the following expression:

Order Dates: ConcatRelated("[Order Date]","[qry_2009Orders]","[qry_2009Orders].

[Customer ID]=""" & [Customers].[Customer Id] & """")

Using the above expression, the Microsoft Access database custom VBA function is called and the parameters are passed into the VBA procedure which is executed as explained from Allen Browne’s web page.

The fact we can use a query (or table) as the second parameters of the function ConcateRelated means we filter at source as a pre step to handling any criteria within the main query and satisfy the VBA function.

For larger queries, there will need to be a balance of using high data volume and nesting queries as the system will have to work harder to return data (recordset results) hence the need to use field indexing effectively – What does that all mean? 

Why not take a look at and study my eBooks for a better understanding of the theories and best working practices.

Tags: access vba custom function, concatenate VBA code, how to concatenate into a single line, microsoft access database, MS Access, using ms access queries