473,326 Members | 2,588 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

How to Display Intermediate Cumulative Sum By Page Footer

Hi,

On a report, how to display
  1. Intermediate cumulative sums by page footer, that is, on
    • Page 1, sum of displayed records ,
    • page 2, sum of displayed records + Page 1 records ,
    • And so on

Thanks for your help
Jun 12 '16 #1
5 1679
PhilOfWalton
1,430 Expert 1GB
I thought that I had seen your database, but it is no longer there.

Never mind.

Let us assume you have a running total for each detail line of your report, and let us assume that it is called Text152. Let us assume that in the page footer you have a control called Text164 whose ControlSource = "=Text152"

I shall digress here, because gaps in field names in table and gaps in table names are bad news, as everything has to be surrounded by square brackets for use in queries. Equally Text152 & Text164 are equally unhelpful.
A final bit of advice is that all modules should start with

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
The Option Explicit means that you have to declare your variables which is good practice, and will give a compile error if you make a statement with an incorrect variable spelling.

Back to the problem.
In the Page header section of your report create a text field called "CarriedForward"

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4.     Dim BF As Currency
  5.     Dim PageFooterPrinted As Boolean
  6.  
  7. Private Sub PageFooterSection_Format(Cancel As Integer, FormatCount As Integer)
  8.  
  9.     BF = Text164
  10.  
  11. End Sub
  12.  
  13. Private Sub PageFooterSection_Print(Cancel As Integer, PrintCount As Integer)
  14.  
  15.     PageFooterPrinted = True
  16.     If Page = Pages Then                ' Last page
  17.         PageFooterPrinted = False
  18.         BF = 0
  19.     End If
  20.  
  21. End Sub
  22.  
  23. Private Sub PageHeaderSection_Format(Cancel As Integer, FormatCount As Integer)
  24.  
  25.     ' Only print the CF amount if a Page Footer has previously been printed
  26.     ' and there is a Broght Forward balance
  27.     If PageFooterPrinted = True Then
  28.         If BF > 0 Then
  29.             CarriedForward = "CF:" & CStr(Format(BF, "Currency"))
  30.         Else
  31.             CarriedForward = ""
  32.         End If
  33.     Else
  34.         CarriedForward = ""
  35.     End If
  36.  
  37. End Sub
  38.  
  39. Private Sub Report_Load()
  40.  
  41.     ' Initialise
  42.     BF = 0
  43.     PageFooterPrinted = False
  44.  
  45.     Me.Text331.Visible = False
  46.     Me.Text326.Visible = False
  47.     Me.Text308.Visible = False
  48.     Me.Text329.Visible = False
  49.     Me.Text318.Visible = False
  50.     Me.Text316.Visible = False
  51.     Me.Text312.Visible = False
  52.  
  53. End Sub
  54.  
  55.  
So what happens is that on opening the report (in Print Preview) the Brought forward balance is initialised.
As each Page Footer is Printed (NOT Formatted, because until it is printed, nothing is really fixed) we save the balance.
As each Page Header is formatted, unless it's Page 1, we print the Balance we have just saved.

Phil
Jun 12 '16 #2
Hi PhilOfWalton

Thanks for your answer. I find an other way by using textbox cumul property as follow
  1. In detail area
    • A textBox named txtAmount with Control source = amount, bound field with report.source = Table/requery...
    • CumulAmount text box named txtCumulAmount with properties as follows
      • Hidden field
      • ControlSource = txtAmount
      • Cumul = Continue
  2. In Page footer
    • A field with ControlSource = txtCumulAmount with cumul=no


click here to upload an access db example
Jun 13 '16 #3
PhilOfWalton,

I need to get invisible the page footer on last page of a report, but I don't know how to proceed, an idea?

Thanks by advance
Jun 13 '16 #4
PhilOfWalton
1,430 Expert 1GB
Why?

If you want you can change the label that says "Balance Carried Forward.."
to "Total:"

In the OnFormat of the Page Footer add
Expand|Select|Wrap|Line Numbers
  1.     If Page = Pages Then
  2.         Label168.Caption = "Total:"
  3.     End If
  4.  
If you really do want to hide it, use
Expand|Select|Wrap|Line Numbers
  1.     If Page = Pages Then
  2.         Me.Section(acPageFooter).Visible = False
  3.     End If
  4.  
Incidentally, although it's a hell of a job, you need to create Normalised Access tables. What you have, is I suspect an Excel sheet, riddled with errors and inconsistencies.
For example there are at least 3 spellings of Air Conditioning and they are either sold by “Unit”, “Units” or “PC”

Basically any piece of information that appears more than 2 or 3 times should be in it’s own table

Phil
Jun 13 '16 #5
Thanks for your very precious help PhilOfWalton.

with your code, I build my report as I want

Once again thanks a lot
Jun 13 '16 #6

Sign in to post your reply or Sign up for a free account.

Similar topics

3
by: John | last post by:
I have a report that's basically a calendar. I need to get a footer that shows the range of dates on the current page (a start date and end date). I know how to do that by putting a field in the...
3
by: manning_news | last post by:
Using A2K. I've got a page footer that is displayed only on the last page of the report. There is a subreport, text boxes, and a line for signature in this footer, in that order. If the...
1
by: Alberto | last post by:
How can I do in Crystal Reports to show the page footer in all the pages but not in the last one? Thank you
3
by: Chad THomas | last post by:
I am wanting to diplay a progress page with some eye candy on it to entertain a user while I am processing their request. Does anyone know what is the best way in asp.net to do this?
1
by: CaptainDahlin | last post by:
I know the basics about access reports and putting page totals in the page footers. What I can't figure out is along with the current page total to display the previous page total: At the bottom...
2
by: CaptainDahlin | last post by:
I know the basics about access reports and putting page totals in the page footers. What I can't figure out is along with the current page total to display the previous page total: At the bottom...
3
by: fonzie | last post by:
On my report I am currently using page footer to display some disclaimer information. No matter how many records show up in the detail section, the page footer is always at the bottom of the page....
0
by: voroojak | last post by:
I have a problem in my page footer in report service. in print preview it is showing that it is ok and it is fitting on one page but when i print just half top of my page footer is printed. any...
0
by: pratik.best | last post by:
Hi, my problem is very simple yet interesting. In Crystal Reports the page footer appear at the bottom of the page, but I want it to appear just after the contents of the page. How can I do that?...
4
by: sjb67 | last post by:
Hello, I am trying to create an invoice sub-report (this invoice lines). Some customers get discounts, but for those that don't I wish not to show the RRP and discount controls. My sub-report...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.