Grouping and page header report

[Tara]

I have a report that groups by county and service within that county. Each
county has a total billing amount that shows up in the page header. I have
the page header set to appear on each page. What I need though is for the
total billing amount to only show up in the header of the first page for that
county, yet each page must still have it's own header. Can this be done? I
actually asked this question a week or so ago, but I didn't explain clearly
enough what I needed. If this can't be done, could I instead have the report
run multiple times with the push of a single command button with each county
generating it's own report ? Any input is welcome.

 

[Marshall Barton]

I have a report that groups by county and service within that county. Each
county has a total billing amount that shows up in the page header. I have
the page header set to appear on each page. What I need though is for the
total billing amount to only show up in the header of the first page for that
county, yet each page must still have it's own header.

In some versions of Access, getting the group total into the
page header is the tough part. How'd you do that and in
what version of Access?

To hide a text box for all but the first page, you need to
know when the page header is working with the first detail
or some other detail. This can be done by adding a (hidden)
text box (named txtLineNum) to the detail section. Set its
expression to =1 and RunningSum ro Over Group.

Then the page header text box can be hidden by using a line
of code in the detail section's Print (or Format) event:
Me.[page header text box].Visible = (txtLineNum = 1)

If the page header text box can get away with displaying
Null instead of being invisible, you might not even need the
line of code. Just set its expression to:
=IIf(txtLineNum = 1, your group total expression, Null)
but maybe your method of getting the group total into the
page header won't allow for that?

 

[Marshall Barton]

I have a report that groups by county and service within that county. Each
county has a total billing amount that shows up in the page header. I have
the page header set to appear on each page. What I need though is for the
total billing amount to only show up in the header of the first page for that
county, yet each page must still have it's own header.

In some versions of Access, getting the group total into the
page header is the tough part. How'd you do that and in
what version of Access?

To hide a text box for all but the first page, you need to
know when the page header is working with the first detail
or some other detail. This can be done by adding a (hidden)
text box (named txtLineNum) to the detail section. Set its
expression to =1 and RunningSum ro Over Group.

Then the page header text box can be hidden by using a line
of code in the detail section's Print (or Format) event:
Me.[page header text box].Visible = (txtLineNum = 1)

If the page header text box can get away with displaying
Null instead of being invisible, you might not even need the
line of code. Just set its expression to:
=IIf(txtLineNum = 1, your group total expression, Null)
but maybe your method of getting the group total into the
page header won't allow for that?

 

[Tara]

Sorry it took so long to check this for a response. I was gone all weekend.

Anyway, as for getting the group total in the page header, I created a query
that calculated the totals, then added it to the query that was originally
feeding the report. I'm using Access 2000.

Your solutions sound easy enough...I'm going to try this this afternoon and
I'll let you know how it goes.

Thanks!

I have a report that groups by county and service within that county. Each
county has a total billing amount that shows up in the page header. I have
the page header set to appear on each page. What I need though is for the
total billing amount to only show up in the header of the first page for that
county, yet each page must still have it's own header.

In some versions of Access, getting the group total into the
page header is the tough part. How'd you do that and in
what version of Access?

To hide a text box for all but the first page, you need to
know when the page header is working with the first detail
or some other detail. This can be done by adding a (hidden)
text box (named txtLineNum) to the detail section. Set its
expression to =1 and RunningSum ro Over Group.

Then the page header text box can be hidden by using a line
of code in the detail section's Print (or Format) event:
Me.[page header text box].Visible = (txtLineNum = 1)

If the page header text box can get away with displaying
Null instead of being invisible, you might not even need the
line of code. Just set its expression to:
=IIf(txtLineNum = 1, your group total expression, Null)
but maybe your method of getting the group total into the
page header won't allow for that?

 

[Tara]

Sorry it took so long to check this for a response. I was gone all weekend.

Anyway, as for getting the group total in the page header, I created a query
that calculated the totals, then added it to the query that was originally
feeding the report. I'm using Access 2000.

Your solutions sound easy enough...I'm going to try this this afternoon and
I'll let you know how it goes.

Thanks!

I have a report that groups by county and service within that county. Each
county has a total billing amount that shows up in the page header. I have
the page header set to appear on each page. What I need though is for the
total billing amount to only show up in the header of the first page for that
county, yet each page must still have it's own header.

In some versions of Access, getting the group total into the
page header is the tough part. How'd you do that and in
what version of Access?

To hide a text box for all but the first page, you need to
know when the page header is working with the first detail
or some other detail. This can be done by adding a (hidden)
text box (named txtLineNum) to the detail section. Set its
expression to =1 and RunningSum ro Over Group.

Then the page header text box can be hidden by using a line
of code in the detail section's Print (or Format) event:
Me.[page header text box].Visible = (txtLineNum = 1)

If the page header text box can get away with displaying
Null instead of being invisible, you might not even need the
line of code. Just set its expression to:
=IIf(txtLineNum = 1, your group total expression, Null)
but maybe your method of getting the group total into the
page header won't allow for that?

 

[Tara]

Well Marshall, I think I almost have it. The solution you gave is closer
than I've gotten before. It's doing something strange though so I'm hoping
you can help me out one more time. The total now shows up on the first page
of the first county and the first page of the second county but not on any of
those counties subsequent pages - which is perfect. However, it doesn't show
up on the first pages of the next 2 counties. Then it shows up again when
the billable service changes. And then it begins it's wonky pattern again.
Any ideas?

Thanks so much!

I have a report that groups by county and service within that county. Each
county has a total billing amount that shows up in the page header. I have
the page header set to appear on each page. What I need though is for the
total billing amount to only show up in the header of the first page for that
county, yet each page must still have it's own header.

In some versions of Access, getting the group total into the
page header is the tough part. How'd you do that and in
what version of Access?

To hide a text box for all but the first page, you need to
know when the page header is working with the first detail
or some other detail. This can be done by adding a (hidden)
text box (named txtLineNum) to the detail section. Set its
expression to =1 and RunningSum ro Over Group.

Then the page header text box can be hidden by using a line
of code in the detail section's Print (or Format) event:
Me.[page header text box].Visible = (txtLineNum = 1)

If the page header text box can get away with displaying
Null instead of being invisible, you might not even need the
line of code. Just set its expression to:
=IIf(txtLineNum = 1, your group total expression, Null)
but maybe your method of getting the group total into the
page header won't allow for that?

 

[Tara]

Well Marshall, I think I almost have it. The solution you gave is closer
than I've gotten before. It's doing something strange though so I'm hoping
you can help me out one more time. The total now shows up on the first page
of the first county and the first page of the second county but not on any of
those counties subsequent pages - which is perfect. However, it doesn't show
up on the first pages of the next 2 counties. Then it shows up again when
the billable service changes. And then it begins it's wonky pattern again.
Any ideas?

Thanks so much!

I have a report that groups by county and service within that county. Each
county has a total billing amount that shows up in the page header. I have
the page header set to appear on each page. What I need though is for the
total billing amount to only show up in the header of the first page for that
county, yet each page must still have it's own header.

In some versions of Access, getting the group total into the
page header is the tough part. How'd you do that and in
what version of Access?

To hide a text box for all but the first page, you need to
know when the page header is working with the first detail
or some other detail. This can be done by adding a (hidden)
text box (named txtLineNum) to the detail section. Set its
expression to =1 and RunningSum ro Over Group.

Then the page header text box can be hidden by using a line
of code in the detail section's Print (or Format) event:
Me.[page header text box].Visible = (txtLineNum = 1)

If the page header text box can get away with displaying
Null instead of being invisible, you might not even need the
line of code. Just set its expression to:
=IIf(txtLineNum = 1, your group total expression, Null)
but maybe your method of getting the group total into the
page header won't allow for that?

 

[Marshall Barton]

I provided two solutions, which one are you using?

The problem you are now seeing sounds like there is a small.
blank piece of the bottom of a detail on the page where a
new county starts. Make sure the County group header
section's ForceNewPage property is set to BeforeSection.

If that is not sufficient, you may need to set the detail
section's KeepTogether property to Yes.
--
Marsh
MVP [MS Access]

Well Marshall, I think I almost have it. The solution you gave is closer
than I've gotten before. It's doing something strange though so I'm hoping
you can help me out one more time. The total now shows up on the first page
of the first county and the first page of the second county but not on any of
those counties subsequent pages - which is perfect. However, it doesn't show
up on the first pages of the next 2 counties. Then it shows up again when
the billable service changes. And then it begins it's wonky pattern again.

I have a report that groups by county and service within that county. Each
county has a total billing amount that shows up in the page header. I have
the page header set to appear on each page. What I need though is for the
total billing amount to only show up in the header of the first page for that
county, yet each page must still have it's own header.

In some versions of Access, getting the group total into the
page header is the tough part. How'd you do that and in
what version of Access?

To hide a text box for all but the first page, you need to
know when the page header is working with the first detail
or some other detail. This can be done by adding a (hidden)
text box (named txtLineNum) to the detail section. Set its
expression to =1 and RunningSum ro Over Group.

Then the page header text box can be hidden by using a line
of code in the detail section's Print (or Format) event:
Me.[page header text box].Visible = (txtLineNum = 1)

If the page header text box can get away with displaying
Null instead of being invisible, you might not even need the
line of code. Just set its expression to:
=IIf(txtLineNum = 1, your group total expression, Null)
but maybe your method of getting the group total into the
page header won't allow for that?

 

[Marshall Barton]

I provided two solutions, which one are you using?

The problem you are now seeing sounds like there is a small.
blank piece of the bottom of a detail on the page where a
new county starts. Make sure the County group header
section's ForceNewPage property is set to BeforeSection.

If that is not sufficient, you may need to set the detail
section's KeepTogether property to Yes.
--
Marsh
MVP [MS Access]

Well Marshall, I think I almost have it. The solution you gave is closer
than I've gotten before. It's doing something strange though so I'm hoping
you can help me out one more time. The total now shows up on the first page
of the first county and the first page of the second county but not on any of
those counties subsequent pages - which is perfect. However, it doesn't show
up on the first pages of the next 2 counties. Then it shows up again when
the billable service changes. And then it begins it's wonky pattern again.

I have a report that groups by county and service within that county. Each
county has a total billing amount that shows up in the page header. I have
the page header set to appear on each page. What I need though is for the
total billing amount to only show up in the header of the first page for that
county, yet each page must still have it's own header.

In some versions of Access, getting the group total into the
page header is the tough part. How'd you do that and in
what version of Access?

To hide a text box for all but the first page, you need to
know when the page header is working with the first detail
or some other detail. This can be done by adding a (hidden)
text box (named txtLineNum) to the detail section. Set its
expression to =1 and RunningSum ro Over Group.

Then the page header text box can be hidden by using a line
of code in the detail section's Print (or Format) event:
Me.[page header text box].Visible = (txtLineNum = 1)

If the page header text box can get away with displaying
Null instead of being invisible, you might not even need the
line of code. Just set its expression to:
=IIf(txtLineNum = 1, your group total expression, Null)
but maybe your method of getting the group total into the
page header won't allow for that?

 

[Tara]

It's working now. I was using the first solution when I posted my last
question. I had tried the second one, but it didn't work either at first.
What I ended up doing was creating a second text box on the page header that
had it's control source set to the the billing total and made it invisible.
Then I used your solution of setting the original textbox in the page header
to =IIf(txtLineNum = 1, BillingTotal, Null). Not sure why, but that took
care of the issue.

Thanks so much for the help!

I provided two solutions, which one are you using?

The problem you are now seeing sounds like there is a small.
blank piece of the bottom of a detail on the page where a
new county starts. Make sure the County group header
section's ForceNewPage property is set to BeforeSection.

If that is not sufficient, you may need to set the detail
section's KeepTogether property to Yes.
--
Marsh
MVP [MS Access]

Well Marshall, I think I almost have it. The solution you gave is closer
than I've gotten before. It's doing something strange though so I'm hoping
you can help me out one more time. The total now shows up on the first page
of the first county and the first page of the second county but not on any of
those counties subsequent pages - which is perfect. However, it doesn't show
up on the first pages of the next 2 counties. Then it shows up again when
the billable service changes. And then it begins it's wonky pattern again.

Tara wrote:

I have a report that groups by county and service within that county. Each
county has a total billing amount that shows up in the page header. I have
the page header set to appear on each page. What I need though is for the
total billing amount to only show up in the header of the first page for that
county, yet each page must still have it's own header.


In some versions of Access, getting the group total into the
page header is the tough part. How'd you do that and in
what version of Access?

To hide a text box for all but the first page, you need to
know when the page header is working with the first detail
or some other detail. This can be done by adding a (hidden)
text box (named txtLineNum) to the detail section. Set its
expression to =1 and RunningSum ro Over Group.

Then the page header text box can be hidden by using a line
of code in the detail section's Print (or Format) event:
Me.[page header text box].Visible = (txtLineNum = 1)

If the page header text box can get away with displaying
Null instead of being invisible, you might not even need the
line of code. Just set its expression to:
=IIf(txtLineNum = 1, your group total expression, Null)
but maybe your method of getting the group total into the
page header won't allow for that?

 

[Tara]

It's working now. I was using the first solution when I posted my last
question. I had tried the second one, but it didn't work either at first.
What I ended up doing was creating a second text box on the page header that
had it's control source set to the the billing total and made it invisible.
Then I used your solution of setting the original textbox in the page header
to =IIf(txtLineNum = 1, BillingTotal, Null). Not sure why, but that took
care of the issue.

Thanks so much for the help!

I provided two solutions, which one are you using?

The problem you are now seeing sounds like there is a small.
blank piece of the bottom of a detail on the page where a
new county starts. Make sure the County group header
section's ForceNewPage property is set to BeforeSection.

If that is not sufficient, you may need to set the detail
section's KeepTogether property to Yes.
--
Marsh
MVP [MS Access]

Well Marshall, I think I almost have it. The solution you gave is closer
than I've gotten before. It's doing something strange though so I'm hoping
you can help me out one more time. The total now shows up on the first page
of the first county and the first page of the second county but not on any of
those counties subsequent pages - which is perfect. However, it doesn't show
up on the first pages of the next 2 counties. Then it shows up again when
the billable service changes. And then it begins it's wonky pattern again.

Tara wrote:

I have a report that groups by county and service within that county. Each
county has a total billing amount that shows up in the page header. I have
the page header set to appear on each page. What I need though is for the
total billing amount to only show up in the header of the first page for that
county, yet each page must still have it's own header.


In some versions of Access, getting the group total into the
page header is the tough part. How'd you do that and in
what version of Access?

To hide a text box for all but the first page, you need to
know when the page header is working with the first detail
or some other detail. This can be done by adding a (hidden)
text box (named txtLineNum) to the detail section. Set its
expression to =1 and RunningSum ro Over Group.

Then the page header text box can be hidden by using a line
of code in the detail section's Print (or Format) event:
Me.[page header text box].Visible = (txtLineNum = 1)

If the page header text box can get away with displaying
Null instead of being invisible, you might not even need the
line of code. Just set its expression to:
=IIf(txtLineNum = 1, your group total expression, Null)
but maybe your method of getting the group total into the
page header won't allow for that?