Show x field per page

[jca]

I would like to know if there's a way to limit the number of line a report show per page. Like, if I have 30 items and I want to see only 20 items per page. Then the total for that page and the next 10 items on another page and the total for only that second page. Is it possible ?

 

[Gootz11]

The easiest thing you can do is import the report to
word or excel and then put in the page breaks

 

[jca]

I can't do that cause everything must be automatic.
Each page of the report must be uniform cause it's printing bills.

 

[Gootz11]

I just thought of something you could do, if you want you can set up 2 or 3 different reports that do the same thing but only return the values you want. Exmp: run a query that retrurns all values less than 20 and then run another query that retruns values between 20 and 40 and so on. build a macro to run/print all reports at the same time. the only other alternative is to write your own code.

 

[jca]

It's a good idea but how can I make a query that returns only 20 values ?

 

[Rich]

What was wrong with Pats suggestion?

 

[jca]

There's nothing wrong with Pat's suggestion. Except I must code some VBA and I'm the only one here doing VBA and I'm leaving the company for a couple of months soon. So no one will be able to support the db after I leave. But I think I can make some simple enough codes so it will be easily learn.

 

[Rich]

The first part of Pat's suggestion needs no code, the answer to the second is here Article ID: Q132017
Building queries for each twenty records would be unrealistic and un-manageable

 

[jca]

My only problem with Pat's first solution is that when I have let's say 25 lines, the last 5 lines go to the next page which is good. But on that next page, there's not enough data to fill the whole page. So, all the sums goes up to the middle of the page.

But I've made a solution that is working right now. Here it goes if you want to know it:

1- I create a table with all the records I need to put on the bills and I add one column, Group, which I will use later.

2- I use some code to run through the table and insert blank field to make it a multiple of 20. Ex.: If I have 6 lines, I'll insert 14. If I have 26, I'll insert 14.

3- With some more code, I reopen the table from 2 and I assign a group number based on the record count. So, if I have 24 lines for the bills #23433, I will have 20 "good" lines in group 1 and 4 "good" lines and 16 "blank" lines in group 2.

4- In my report, I regrouped the fields by bill number and then by group. So, I have only 20 lines per page and I can have the sum, taxes, ... for each page.

That's it...

But if someone can come up with a better way to do it or see a big flaw in my logistic, feel free to reply.

 

[Rich]

Did you set the detail section CanShrink to no

 

[jca]

No, why would I set it to no ?

 

[Rich]

You should see in this example that the date and page number stay in the same place at the bottom of the page

 

[jca]

I see what you were saying but I can't use this solution cause I need the sum of the page and you can't have a sum in the footer