Concatenate a list into one field

[ijswalker]

Hi,

I have a list of services that pertain to a client ID. One record for each service with a date the service was performed. Does anyone know of a piece of VBA code that will concatenate all of the service dates for each client, create a new table and list the result by Client ID based on a selected month.

Thanks

Ian

 

[pbaldy]

This type of thing?

http://www.mvps.org/access/modules/mdl0004.htm

http://support.microsoft.com/kb/210163/en-us

 

[ijswalker]

Thanks,

I checked out the link at now I can do exactly what I wanted. The only piece that I can't get to work is the Can Grow property.

Thanks

Ian

 

[datAdrenaline]

Are you setting CanGrow on a control contained in a Form? .. If so, the CanGrow will have NO EFFECT whilst viewing the records through that form. The property is question will only be considered when you tell Access to PRINT that form ... but to me, Forms are not made for printing ... thats what reports are for ...

 

[ijswalker]

Hi dataAdrenaline,

It is a report that I am trying to print. I have the detail in the report but when there are more than 3 items it cuts them off. Is there a piece of code to have the field expand in the report to show all of the concatenated detail?

Thanks

Ian

 

[datAdrenaline]

Just make sure the control that is containing the CSV in is set to CanGrow = Yes (True), also, make sure the section that the control is contained with in on the Report is set to CanGrow = Yes (True)

 

[ijswalker]

Hi datAdenaline,

I've set the section and the fields in the report to CanGrow = True. Still not working. Is there a way to set this into the VBA?
I have this code in the OnFormat Detail section of the report. This looks at the Date field in detail section and then concatenates the dates in a field called AllProducts in the Group section.
The AllProducts field in the report is unbound and it does what I need it to do other than expands to show all of the data. Is there a way that I could maybe have a query that does the same thing or make a table that will format the data in this way, then I could reference that to the report? Hope that makes sense? Here is the code I have in he OnFormat section of the report.

Thanks

Ian

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
On Local Error GoTo Detail_Format_Err
   If Not FirstPass Then
Me!AllProducts = Me![DATE]
FirstPass = True
   Else
Me!AllProducts = Me!AllProducts & ", " & Me![DATE]
   End If
Detail_Format_End:
   Exit Sub
Detail_Format_Err:
   MsgBox Error$
   Resume Detail_Format_End

 

[ijswalker]

Hi,

After further investigation - The CanGrow property in working on some records in the report but not others. Really strange and haven't a clue why it would do this. As anyone had issues with this before?

Thanks

Ian

 

[datAdrenaline]

... Can you post a copy of your db? .. you can mask the data so it is meaningless if you wish.