Report Sum question

Hi all

I have a report that shows output from a League Table

Most years have just Played/Won/Drawn/Lost/For/Against/Points format

However, a handful of years had an added Bonus Point feature, where
the team who had the highest aggregate score over the home and away
ties got a bonus point. In a year where there are no bonuses the field
in the table is left blank, whereas bonus years are recorded 0, 1, 2,
3 etc etc

I have figured out how, on my report to sum the points and bonuses
using =Nz([Points],0)+Nz([Bonus Points],0).
The report is all encompassing of all years (one year per page)
I would ideally like the Totals column to not do the sum above and be
blank/invisible if there is no number in the Bonus Points Field and
ideally the column headers BP (for Bonus Points) and Total (for the
above sum) to be invisible if the Bonus field is blank
IE the column headers for a non bonus year would show
Team Played Won Drew Lost For Against Points
and a Bonus year
Team Played Won Drew Lost For Against Points BP Total

Is this possible?
Many thanks for your ideas
Neil

wrote:
I have a report that shows output from a League Table

Most years have just Played/Won/Drawn/Lost/For/Against/Points format

However, a handful of years had an added Bonus Point feature, where
the team who had the highest aggregate score over the home and away
ties got a bonus point. In a year where there are no bonuses the field
in the table is left blank, whereas bonus years are recorded 0, 1, 2,
3 etc etc

I have figured out how, on my report to sum the points and bonuses
using =Nz([Points],0)+Nz([Bonus Points],0).
The report is all encompassing of all years (one year per page)
I would ideally like the Totals column to not do the sum above and be
blank/invisible if there is no number in the Bonus Points Field and
ideally the column headers BP (for Bonus Points) and Total (for the
above sum) to be invisible if the Bonus field is blank
IE the column headers for a non bonus year would show
Team Played Won Drew Lost For Against Points
and a Bonus year
Team Played Won Drew Lost For Against Points BP Total


Try adding a hidden text box (named txtTotalBP) to the year
group header section. Set its expression to:
=Sum([Bonus Points])
Then add some code to the group header section's Format
event pvent procedure to hide the things you don't want to
see when there are no bonus points:

Me.txtBP.Visible = Not IsNull(Me.txtTotalBP)
Me.lblBP.Visible = Not IsNull(Me.txtTotalBP)
Me.txtTotal.Visible = Not IsNull(Me.txtTotalBP)

There's probably a little more to it than that so post back
with more details if you need further assistance.

--
Marsh
MVP [MS Access]

On 15 Apr, 21:06, Marshall Barton wrote:
wrote:
I have a report that shows output from a League Table

Most years have just Played/Won/Drawn/Lost/For/Against/Points format

However, a handful of years had an added Bonus Point feature, where
the team who had the highest aggregate score over the home and away
ties got a bonus point. In a year where there are no bonuses the field
in the table is left blank, whereas bonus years are recorded 0, 1, 2,
3 etc etc

I have figured out how, on my report to sum the points and bonuses
using =Nz([Points],0)+Nz([Bonus Points],0).
The report is all encompassing of all years (one year per page)
I would ideally like the Totals column to not do the sum above and be
blank/invisible if there is no number in the Bonus Points Field and
ideally the column headers BP (for Bonus Points) and Total (for the
above sum) to be invisible if the Bonus field is blank
IE the column headers for a non bonus year would show
Team Played Won Drew Lost For Against Points
and a Bonus year
Team Played Won Drew Lost For Against Points BP Total

Try adding a hidden text box (named txtTotalBP) to the year
group header section. *Set its expression to:
* * * * =Sum([Bonus Points])
Then add some code to the group header section's Format
event pvent procedure to hide the things you don't want to
see when there are no bonus points:

* * * * Me.txtBP.Visible = Not IsNull(Me.txtTotalBP)
* * * * Me.lblBP.Visible = Not IsNull(Me.txtTotalBP)
* * * * Me.txtTotal.Visible = Not IsNull(Me.txtTotalBP)

There's probably a little more to it than that so post back
with more details if you need further assistance.

--
Marsh
MVP [MS Access]- Hide quoted text -

- Show quoted text -

Thanks Marsh
I get compiler errors if I try this
In my header the Column Headers in question are
Name:Bonus Points_Label
Caption:BP
and
Name:Label65
Caption:Total

I presume these have a bearing on what you showed me

My Header says 'League Header' on the Report and it's properties a
Name: GroupHeader1

Thanks
Neil

wrote:

On 15 Apr, 21:06, Marshall Barton wrote:
wrote:
I have a report that shows output from a League Table

Most years have just Played/Won/Drawn/Lost/For/Against/Points format

However, a handful of years had an added Bonus Point feature, where
the team who had the highest aggregate score over the home and away
ties got a bonus point. In a year where there are no bonuses the field
in the table is left blank, whereas bonus years are recorded 0, 1, 2,
3 etc etc

I have figured out how, on my report to sum the points and bonuses
using =Nz([Points],0)+Nz([Bonus Points],0).
The report is all encompassing of all years (one year per page)
I would ideally like the Totals column to not do the sum above and be
blank/invisible if there is no number in the Bonus Points Field and
ideally the column headers BP (for Bonus Points) and Total (for the
above sum) to be invisible if the Bonus field is blank
IE the column headers for a non bonus year would show
Team Played Won Drew Lost For Against Points
and a Bonus year
Team Played Won Drew Lost For Against Points BP Total

Try adding a hidden text box (named txtTotalBP) to the year
group header section. *Set its expression to:
* * * * =Sum([Bonus Points])
Then add some code to the group header section's Format
event pvent procedure to hide the things you don't want to
see when there are no bonus points:

* * * * Me.txtBP.Visible = Not IsNull(Me.txtTotalBP)
* * * * Me.lblBP.Visible = Not IsNull(Me.txtTotalBP)
* * * * Me.txtTotal.Visible = Not IsNull(Me.txtTotalBP)

There's probably a little more to it than that so post back
with more details if you need further assistance.


I get compiler errors if I try this
In my header the Column Headers in question are
Name:Bonus Points_Label
Caption:BP
and
Name:Label65
Caption:Total

I presume these have a bearing on what you showed me

My Header says 'League Header' on the Report and it's properties a
Name: GroupHeader1


I guess that you did not change the made up names I used to
the names you really have. Then point I was trying to make
is that once you add the txtTotalBPtext box to the group
header section, you can hide whatever you want by using a
line of code like:
Me.xxxxxxxx.Visible = Not IsNull(Me.txtTotalBP)

You can usually get more detailed information about a
compile error and which line has the error when you are in
the VBA Editor and use the Debug - Compile menu item.

Do NOT edit code when the report is being previewed. ALWAYS
switch back to design view before changing anything in the
report's module.

--
Marsh
MVP [MS Access]

On 16 Apr, 14:20, Marshall Barton wrote:
wrote:
On 15 Apr, 21:06, Marshall Barton *wrote:
wrote:
I have a report that shows output from a League Table

Most years have just Played/Won/Drawn/Lost/For/Against/Points format

However, a handful of years had an added Bonus Point feature, where
the team who had the highest aggregate score over the home and away
ties got a bonus point. In a year where there are no bonuses the field
in the table is left blank, whereas bonus years are recorded 0, 1, 2,
3 etc etc

I have figured out how, on my report to sum the points and bonuses
using =Nz([Points],0)+Nz([Bonus Points],0).
The report is all encompassing of all years (one year per page)
I would ideally like the Totals column to not do the sum above and be
blank/invisible if there is no number in the Bonus Points Field and
ideally the column headers BP (for Bonus Points) and Total (for the
above sum) to be invisible if the Bonus field is blank
IE the column headers for a non bonus year would show
Team Played Won Drew Lost For Against Points
and a Bonus year
Team Played Won Drew Lost For Against Points BP Total

Try adding a hidden text box (named txtTotalBP) to the year
group header section. *Set its expression to:
* * * * =Sum([Bonus Points])
Then add some code to the group header section's Format
event pvent procedure to hide the things you don't want to
see when there are no bonus points:

* * * * Me.txtBP.Visible = Not IsNull(Me.txtTotalBP)
* * * * Me.lblBP.Visible = Not IsNull(Me.txtTotalBP)
* * * * Me.txtTotal.Visible = Not IsNull(Me.txtTotalBP)

There's probably a little more to it than that so post back
with more details if you need further assistance.

I get compiler errors if I try this
In my header the Column Headers in question are
Name:Bonus Points_Label
Caption:BP
and
Name:Label65
Caption:Total

I presume these have a bearing on what you showed me

My Header says 'League Header' on the Report and it's properties a
Name: GroupHeader1

I guess that you did not change the made up names I used to
the names you really have. *Then point I was trying to make
is that once you add the txtTotalBPtext box to the group
header section, you can hide whatever you want by using a
line of code like:
* * * * *Me.xxxxxxxx.Visible = Not IsNull(Me.txtTotalBP)

You can usually get more detailed information about a
compile error and which line has the error when you are in
the VBA Editor and use the Debug - Compile menu item.

Do NOT edit code when the report is being previewed. *ALWAYS
switch back to design view before changing anything in the
report's module.

--
Marsh
MVP [MS Access]- Hide quoted text -

- Show quoted text -

Thanks for the information Marsh

In my case which do I use, the 'Name' or 'Caption' ?
If it's the name, I have a space in it, how do I get round that in the
Me.xxxxx code?

Neil

wrote:

On 16 Apr, 14:20, Marshall Barton wrote:
wrote:
On 15 Apr, 21:06, Marshall Barton *wrote:
wrote:
I have a report that shows output from a League Table

Most years have just Played/Won/Drawn/Lost/For/Against/Points format

However, a handful of years had an added Bonus Point feature, where
the team who had the highest aggregate score over the home and away
ties got a bonus point. In a year where there are no bonuses the field
in the table is left blank, whereas bonus years are recorded 0, 1, 2,
3 etc etc

I have figured out how, on my report to sum the points and bonuses
using =Nz([Points],0)+Nz([Bonus Points],0).
The report is all encompassing of all years (one year per page)
I would ideally like the Totals column to not do the sum above and be
blank/invisible if there is no number in the Bonus Points Field and
ideally the column headers BP (for Bonus Points) and Total (for the
above sum) to be invisible if the Bonus field is blank
IE the column headers for a non bonus year would show
Team Played Won Drew Lost For Against Points
and a Bonus year
Team Played Won Drew Lost For Against Points BP Total

Try adding a hidden text box (named txtTotalBP) to the year
group header section. *Set its expression to:
* * * * =Sum([Bonus Points])
Then add some code to the group header section's Format
event pvent procedure to hide the things you don't want to
see when there are no bonus points:

* * * * Me.txtBP.Visible = Not IsNull(Me.txtTotalBP)
* * * * Me.lblBP.Visible = Not IsNull(Me.txtTotalBP)
* * * * Me.txtTotal.Visible = Not IsNull(Me.txtTotalBP)

There's probably a little more to it than that so post back
with more details if you need further assistance.

I get compiler errors if I try this
In my header the Column Headers in question are
Name:Bonus Points_Label
Caption:BP
and
Name:Label65
Caption:Total

I presume these have a bearing on what you showed me

My Header says 'League Header' on the Report and it's properties a
Name: GroupHeader1

I guess that you did not change the made up names I used to
the names you really have. *Then point I was trying to make
is that once you add the txtTotalBPtext box to the group
header section, you can hide whatever you want by using a
line of code like:
* * * * *Me.xxxxxxxx.Visible = Not IsNull(Me.txtTotalBP)

You can usually get more detailed information about a
compile error and which line has the error when you are in
the VBA Editor and use the Debug - Compile menu item.

Do NOT edit code when the report is being previewed. *ALWAYS
switch back to design view before changing anything in the
report's module.


In my case which do I use, the 'Name' or 'Caption' ?
If it's the name, I have a space in it, how do I get round that in the
Me.xxxxx code?


Use the control's Name and if it has a space or other funky
character, you must enclose it in [ ]
Me.[xxx yyy].Visible = ...

--
Marsh
MVP [MS Access]

On 16 Apr, 19:36, Marshall Barton wrote:
wrote:
On 16 Apr, 14:20, Marshall Barton *wrote:
wrote:
On 15 Apr, 21:06, Marshall Barton *wrote:
wrote:
I have a report that shows output from a League Table

Most years have just Played/Won/Drawn/Lost/For/Against/Points format

However, a handful of years had an added Bonus Point feature, where
the team who had the highest aggregate score over the home and away
ties got a bonus point. In a year where there are no bonuses the field
in the table is left blank, whereas bonus years are recorded 0, 1, 2,
3 etc etc

I have figured out how, on my report to sum the points and bonuses
using =Nz([Points],0)+Nz([Bonus Points],0).
The report is all encompassing of all years (one year per page)
I would ideally like the Totals column to not do the sum above and be
blank/invisible if there is no number in the Bonus Points Field and
ideally the column headers BP (for Bonus Points) and Total (for the
above sum) to be invisible if the Bonus field is blank
IE the column headers for a non bonus year would show
Team Played Won Drew Lost For Against Points
and a Bonus year
Team Played Won Drew Lost For Against Points BP Total

Try adding a hidden text box (named txtTotalBP) to the year
group header section. *Set its expression to:
* * * * =Sum([Bonus Points])
Then add some code to the group header section's Format
event pvent procedure to hide the things you don't want to
see when there are no bonus points:

* * * * Me.txtBP.Visible = Not IsNull(Me.txtTotalBP)
* * * * Me.lblBP.Visible = Not IsNull(Me.txtTotalBP)
* * * * Me.txtTotal.Visible = Not IsNull(Me.txtTotalBP)

There's probably a little more to it than that so post back
with more details if you need further assistance.

I get compiler errors if I try this
In my header the Column Headers in question are
Name:Bonus Points_Label
Caption:BP
and
Name:Label65
Caption:Total

I presume these have a bearing on what you showed me

My Header says 'League Header' on the Report and it's properties a
Name: GroupHeader1

I guess that you did not change the made up names I used to
the names you really have. *Then point I was trying to make
is that once you add the txtTotalBPtext box to the group
header section, you can hide whatever you want by using a
line of code like:
* * * * *Me.xxxxxxxx.Visible = Not IsNull(Me.txtTotalBP)

You can usually get more detailed information about a
compile error and which line has the error when you are in
the VBA Editor and use the Debug - Compile menu item.

Do NOT edit code when the report is being previewed. *ALWAYS
switch back to design view before changing anything in the
report's module.

In my case which do I use, the 'Name' or 'Caption' ?
If it's the name, I have a space in it, how do I get round that in the
Me.xxxxx code?

Use the control's Name and if it has a space or other funky
character, you must enclose it in [ ]
* * * * Me.[xxx yyy].Visible = ...

--
Marsh
MVP [MS Access]- Hide quoted text -

- Show quoted text -

Wow!
Got it working.
Many thanks for your help Marsh

Neil