If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
Report Sum question
|
#3
|
|||
|
|||
Report Sum question
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 |
#4
|
|||
|
|||
Report Sum question
|
#5
|
|||
|
|||
Report Sum question
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 |
#6
|
|||
|
|||
Report Sum question
|
#7
|
|||
|
|||
Report Sum question
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 |
Thread Tools | |
Display Modes | |
|
|