concatenate data in one cell in a report/query

[Leo_Polla_Psemata]

Hi
Please watch the below tables.
I have a large ACCESS list as per the left one and I would like to make it as per right.
The problem is that I want to concatenate the "invoices" in one record separated with a coma , .
This is the only way I can compact the long list into a short one and make it comprehensible.
The invoices , in one cell, very rarely are more than 4 or 5 items.
So room for 6 is just perfect.
Hope the below example is self explanatory

 

[EternalMyrtle]

So long as you are not running MS Access 64bit I have had much luck with Allen Browne's concatenation function:

http://allenbrowne.com/func-concat.html

 

[Leo_Polla_Psemata]

Thanks

I have gone through suggestion, copy paste the function,
but i get this error, no need to say i am below novice in vba

The builder

 

[bob fitz]

Are you sure that you have copied the actual code of the function into a General Module.

 

[JANR]

You have named the module with the same name as the function, you can't do that.

Rename the module to something other than ConcatRelated.

ex.
mdlConcatRelated

JR

 

[EternalMyrtle]

@Leo_Polla_Psemata: Did you get manage to get this working?

 

[Leo_Polla_Psemata]

Yes yes yes, i made it... The problem is that when I run this code, the screen freezes for around 45 to 60 seconds.

For the last few years I was struggling to grasp the ideas behind VBA and reach my goals with fewer queries and just now, the last couple of days I managed to work and with 4 different modules.

Thanks everybody.

Of course I still "copy paste" but I make some minor alterations in the code and make the whole project rolling.


Thanks anyway

 

[EternalMyrtle]

Good I had that problem with one of my concatenated fields too and it helped to make a query on a query. So I made a query just to concatenate and then used that query in the form/report's recordsource (another query) instead of trying to use the concatenate function directly. Not sure why it worked but it did.

 

[Leo_Polla_Psemata]

Hmm
In fact I hate "queries on queries and more queries" because if you keep on doing that for a certain period, you come up with hundreds and it is too difficult to control the whole database.
Queries on queries used to be my practice as I have had no idea about vba.
With my first novice steps in VBA this weekend and with your assistance/suggestions, I have transferred my DB in a new one and I am rebuilding it with very few queries and just 6 VBA codes.

This is what I am doing all the weekend. It is so exciting, I've almost forgotten to eat.
I have also experienced the NZ() function, i didn't know it exist, and i have simplified too many convoluted statements. (This NZ comes from another thread, not this one)

I wonder if we can make an update query which will update the concatenate result in a table and will not remove if a new update happens in the same record.
I mean , we have invoices for a specific record and some invoices which relate to it.

2
3
4
After concatenate we update the field and looks like this 2,3,4 that's perfect.

Next week we may get two more invoices for same record, we concatenate on same record, we get two more new invoices
11
14
becomes 11,14
After update we must see on the record 2, 3, 4 , 11,12 (not just 11,12 by replacing the first entry 2,3,4)

Anyway, I will focus on this next week, now i have other priorities.

Thanks anyway