I do believe this will always be the case, because the report footer is not created by Access until all the records hav been displayed. If the first column spills to the second column, there is no longer room at the bottom of the page for the Report Footer. It MUST go to a new page.
I think that is "usually" the case. My experience with reports is that different combinations of things sometimes give different results. I just want to make sure OP is exploring all options.
It could be that if the records are completed in the first Column (by a few records), that the record footer can fit. But, when breaking to the second column, the first column goes to the absolute bottom limit of the column and does not begin to format the report footer (because the data has not completed). The report footer will only go onto a page if there is enough room at the bottom of the page for the entire footer. When the column breaks to the second column, obviously, there are records there. I don't believe Access Reports are "smart" enough to make such adjustments. (One might could fix this programmatically with VBA is the detail OnFormat--but that's another story altogether, and one I am not prepared to address at this moment.)
It would be interesting to note if the Report footer would force a next page if there were exactly enough records to complete one column, but not push to the second column?
I think you are correct though, as no page is being forced with smaller data sets.
Could it be that the Report footer has a new-line at the end of it's text and maybe that is making the Report footer in combination with the records beyond the allowable height of the page? Maybe make the Report footer shorter in height? Maybe make the records top and/or bottom white-space shorter in height?
@Irsmalik.
Perhaps it would be a good idea at this stage to include all those Report Properties that you understand may affect this issue.
If there are more that you aren't aware of then I expect this will be brougght up later but that information is almost certainly necessary to understand your situation properly.
The info we have so far is only part of what the question should be.
May I could not explain my question properly. Please see the attached Report...
This report ends at page 3 at Right side (2nd column). Its Grand Total should be print after the Data finish.
But the Grand Total moves to 4th page. This is my question. The Grand total must be print after the data finish.. either it is it Column 1 (left side) or Column 2 (right side.).
Margins are set as per the required space from top and bottom.
Thanks
irsmalik
Irsmalik. I'm going to assume that your difficulty understanding is due to the language barrier. Please re-read my last post. It is very important that you give us the properties of the Report that you've designed otherwise we're all floundering (bumping around) in the dark. It's very wasteful to have so many people work without providing them the basic information they need.
@irmsalik
Check the highlighted properties of the report footer section of your report. Yours may be different. For example, you may have Force New Page = Before section
Try altering the settings ...one at a time ...till you get what you want.
I do believe this will always be the case, because the report footer is not created by Access until all the records hav been displayed. If the first column spills to the second column, there is no longer room at the bottom of the page for the Report Footer. It MUST go to a new page.
Irsmalik:
Language problem still exist and it will be in future.
Very true. We make allowances for this.
Irsmalik:
Report Properties in jpg format is attached.
Please understand that attached pictures will not be accessible by some users. Where possible, and I've just updated yours to show how, please show the picture as part of the post at least.
Actually, I wasn't asking for all properties, but I do appreciate that you won't always understand what I say clearly so what you've done is plenty good enough ;-)
TwinnyFo:
It MUST go to a new page.
Quite right. Consider how Access handles the first column. It will only start on the second one when the first is full. In this case Full means when it has used up all the available vertical space in the Detail Section. This is determined by the paper height, minus vertical space used before the section, also minus the page footer.
Thus there can never be vertical space, in this scenario, for a Report Footer.
As that is the very point of the answer to the question I've gone ahead and set that as Best Answer.
Hi
Whilst the logic of twinnyfo's post makes total sense, I knew it was possible to do this...so I created a test database to illustrate how its done. Whether it will be suitable for the OP is another matter.
When you split report data into 2 columns, there are two methods of sorting:
1. Down then across (default)
2. Across then down
Using method 1, twinnyfo's comments are 100% true. What the OP wants isn't possible
However using method 2, it does work ...but for exactly the same reasons as it didn't work in method 1. The report footer can be displayed on the same page as the detail section isn't full.
The attached report has 2 columns and 3 pages. The report footer is shown on the final page of the data (page 3).
NOTE: The data is for a fictitious school.
P.S. I've finally worked out how to do inline images here. Unless I'm being dumb, it's not that obvious but it does work!
P.P.S. Happy New Year to all. Lets hope its a good one... without any fear (John Lennon)
I'm not sure it's exactly what the OP asked for, but let's see what they make of it. It's good to know anyway but if they decide it works for them I'll change the Best Answer for the thread.
Frankly, in a situation like this, it would be useful to set both as BA as they both include very pertinent information, but alas only one is allowed.
It's great when people think outside the box and provide answers that are valid (In this case possibly so.) yet without contradicting one saying it can't be done.
PS. Every user has the rights to edit their own posts so if ever you see one of yours with something in it you don't know about you can select Edit and review exactly what is used to get that effect. Another useful resource can be found at BB Code.
Thanks for your time and effort. I really appreciate it.
1. Down then across (default)
2. Across then down
When we use method 2, all the section wise data (I mean Area wise data & its Total) are not inline and scattered. but grand total prints after data is finished.
Method 1 is good, but after data is finished, the grand total moves to next page.
so this is the situation. I prefer method 1 as it describe all the data in proper way with Area and its total. Method 2 is not working as per my requirement.
But I really thank you for you time & effort.
irsmalik
Unless I'm mistaken, I've just realised that all of us missed something obvious....
As already explained by @twinnyfo, a 2 column report arranged down then across will ALWAYS place the report footer on a new page where the report data ends in the right hand column
As I explained above, a workround is to arrange the 2 column report across then down ...but this layout is often unacceptable to users
However there is another solution .... using a subreport!
Attached is a modified version of the example app I uploaded in post #17.
It contains the following 2 column reports with data occupying both columns of 3 pages:
a) rptY7PupilData-AcrossThenDown - report footer is on page 3
b) rptY7PupilData-DownThenAcross - report footer is on its own on page 4
c) rptSubY7PupilData-DownThenAcross - identical to b) so report footer is on page 4 .... if opened as a standalone report
BUT now the 'clever bit' ....
d) rptY7PupilDataMain ...an empty container with subreport from c) - report footer is on right hand column of page 3
OK...so who feels confident to explain the result of d)? There is absolutely no trickery involved
Wow, Dog! I think you hit on something here! I "think" I can explain it that you have the subreport shrunk to half-width with the CanGrow property set to True. It will grow by itself in one column, and then move to the next column. At the end, when it runs out of records, the subreport is still only "half width", but on the next column. Thus it adds the subreport's footer underneath itself.
Hi @twinnyfo
Even if you increase the subreport width to say 19cm, the footer will still be on the right column of page 3
EDIT
In my example, the subreport was 2 columns and the detail section of the main report was also 2 columns
For info, you can change the subreport to single column and say 9cm wide.
However, when used as a subreport, the footer remains on page 3
So...does that strengthen the evidence for your suggested explanation or otherwise?
OK, here's my attempt at explanation. Possibly not that different to yours
It appears that the main form is entirely responsible for controlling the layout. The subform layout is ignored.
As confirmation that the subform column property is utterly irrelevant, keep the subform as two columns and full width.
Make the main form single column so it has enough room to display both columns of the subform.
However, that doesn't happen.
The subform is displayed with the right hand column completely blank and the report will now take up 6 pages
As long as the main form has two columns, it doesn't matter whether the subreport is defined as one column or two
The subreport is treated as a single column and displayed twice side by side.
Hence, the subreport footer will always appear immediately below the final record (in the right or left hand column as appropriate)
I presume Access ignores the subreport layout to prevent any potential issues of conflicting logic
No idea about it being documented as I haven't checked....but I somehow doubt it!
EDIT:
I've done a quick search and not found anything so far.
I did however find this MSDN thread which gives incorrect advice: Subreport with columns
I have just found this article which describes the original problem posed here by the OP and a different more complicated solution: Reports-multiple-columns-and-subreports/
102
