Concatenate List into a Single Text Boxt (abc,def,ghi)

[jkirkland]

I have been researching this but don't understand VBA enough to figure out how to do it on my own.

I have a accounting report grouped by department. It is based on a query that shows department, fund, etc.

I would like a text box under the department name on my report that shows the funds used by that department (101, 102, 103, etc)

I don't know how to get this from a query that currently shows it as:

Dept Fund
1 101
1 102
1 103
2 101
2 103
3 104
3 105

I would like it to show:
_________________________________________________
Department: (1) Administration
_________________________________________________
Funds used by this department: (101, 102, 103)
_________________________________________________

Thank you for the assistance.

 

[nrbeidg]

Here's how I displayed a concatenated string of all of the preferred campsites for a given park using a subreport: (Access 97)

The underlying query returns all of the preferred site numbers for the park specified in the main report.
SELECT tbl_CAMPSITE_Profiles.ParkNo, tbl_CAMPSITE_Profiles.SiteNum
FROM tbl_CAMPSITE_Profiles
WHERE (((tbl_CAMPSITE_Profiles.ParkNo)=[Reports]![rpt_PARKProfile_Main]![DNR_SITE]) AND ((tbl_CAMPSITE_Profiles.PreferredSite)=True))
ORDER BY tbl_CAMPSITE_Profiles.ParkNo, tbl_CAMPSITE_Profiles.PreferredSite;

1. Create a subreport based on the query criteria.
2. In Sorting and Grouping, set
ParkNo: Sort Ascending, with a Group Header and Group Footer.
SiteNum: Sort Ascending, no header or footer.
3. In the Detail section, add the field to be concatenated (SiteNum)
4. Set the Detail property Visible to No.

5. In the ParkNo Group Footer, add an unbound text field (ubPreferredSites) and change it's label caption to "Preferred Sites"

6. Add the following code to the report to display the Preferred Sites: "007, 010, 011, 020, 024, 043, 050, 051, 052"


Option Compare Database 'use the database order for string comparisons
Option Explicit
Dim FirstPass As Integer

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
On Local Error GoTo Detail1_Format_Err
If Not FirstPass Then
Me!ubPreferredSites = Me![SiteNum]
FirstPass = True
Else
Me!ubPreferredSites = Me!ubPreferredSites & ", " & Me![SiteNum]
End If

Detail1_Format_End:
Exit Sub
Detail1_Format_Err:
MsgBox Error$
Resume Detail1_Format_End

End Sub

Private Sub GroupHeaderParkNo_Format(Cancel As Integer, FormatCount As Integer)
Me!ubPreferredSites = Null
FirstPass = False

End Sub

**** For the record, I did not write the original code, and I don't remember where I found it. I just know it made my user VERY happy.

 

[jkirkland]

Thanks! I'll give it a try later today.

 

[jkirkland]

I get an error message that says "Compile Error: Invalid Inside Procedure" and it highlights the first part of the code that says:

Option Compare Database 'use the database order for string comparisons
Option Explicit

I don't suppose you know what I did wrong?

Thanks!

 

[cLFlaVA]

Where do you have these two statements? What block of code?

*cLFlaVA
----------------------------
A polar bear walks into a bar and says, "Can I have a ... beer?"
The bartender asks, "What's with the big pause?

 

[nrbeidg]

This report module code works in Access97. If you are using a different version, you might try taking out the first line.

 

[handlebars]

I think its because you have two Option Explicit statements, one at the begining of the page and one inside the function.

 

[PHV]

The Option instructions must be located in the Declarations section, not inside a procedure body.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244