Getting A Group Footer from Detail Records??

[Craig]

Hi Folks,

I have a report which will have up to 4 detail lines. 1 of the
fields in the detail lines is a location, and there may be 5 of these
for each group.

I want to have one long string with each of these seperated by a " /
", for the group footer.

IE: If I had lines for Oxford, Cambridge, Cheltenham and London I
would want a string in the footer saying:

"Oxford / Cambridge / Cheltenham / London"

Is there an easy way of doing this? I have tried playing around with
the events in the "On Format" of the details section, but don't seem to
be able to do it!!

any help appreciated.


Regards,


Craig.

 

[Allen Browne]

I would advise against using the report events. They do not fire reliably
where a report runs over several pages, if you do not actually print all of
the pages. And it also looks like they won't work in Access 2007 if you use
the new Report View.

Instead, use a function that returns a concatenated string, like the one in
this link:
Return a concatenated list of sub-record values
at:
http://www.mvps.org/access/modules/mdl0004.htm

If you do want to use the report events anyway:

1. Add an unbound text box named (say) txtResult to the Group Footer.

2. Add this to the General Declarations section of the report's module (at
the top, with the Option statements):
Private mstrList As String
Private Const mstrcSep = " / "

3. Add this to the Print event of the Detail section:
If PrintCount = 1 Then
mstrList = mstrList & Me![YourFieldNameHere] & mstrcSep
End If

4. Add this to the Format event of the group footer:
Dim lngLen As Long
lngLen = Len(mstrList) - Len(mstrcSep)
If lngLen > 0 Then
Me.txtResult = Left(mstrList, lngLen)
Else
Me.txtResult = Null
End If
mstrList = vbNullString

 

[Craig]

Allen,

Thanks for your help. It is amazing what a fresh set of eyes can do
- I had closed off the possibility of including it in the query in my
mind!

Thanks again.


Craig.

I would advise against using the report events. They do not fire reliably
where a report runs over several pages, if you do not actually print all of
the pages. And it also looks like they won't work in Access 2007 if you use
the new Report View.

Instead, use a function that returns a concatenated string, like the one in
this link:
Return a concatenated list of sub-record values
at:
http://www.mvps.org/access/modules/mdl0004.htm

If you do want to use the report events anyway:

1. Add an unbound text box named (say) txtResult to the Group Footer.

2. Add this to the General Declarations section of the report's module (at
the top, with the Option statements):
Private mstrList As String
Private Const mstrcSep = " / "

3. Add this to the Print event of the Detail section:
If PrintCount = 1 Then
mstrList = mstrList & Me![YourFieldNameHere] & mstrcSep
End If

4. Add this to the Format event of the group footer:
Dim lngLen As Long
lngLen = Len(mstrList) - Len(mstrcSep)
If lngLen > 0 Then
Me.txtResult = Left(mstrList, lngLen)
Else
Me.txtResult = Null
End If
mstrList = vbNullString

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have a report which will have up to 4 detail lines. 1 of the
fields in the detail lines is a location, and there may be 5 of these
for each group.

I want to have one long string with each of these seperated by a " /
", for the group footer.

IE: If I had lines for Oxford, Cambridge, Cheltenham and London I
would want a string in the footer saying:

"Oxford / Cambridge / Cheltenham / London"

Is there an easy way of doing this? I have tried playing around with
the events in the "On Format" of the details section, but don't seem to
be able to do it!!

any help appreciated.

Regards,

Craig.