Implement Columns in Access report based on groups
I've working on a report that is organized by a Category variable and description. The report is grouped by the CategoryID (hidden) and the CategoryDesc field is displayed in the group header (about 2.75" wide). The detail section of the report contains dates 1-x that correspond to the month selected for the report.
The way the detail section is currently sized, if the month has 31 days, it wraps perfectly so that the next category start at the top of the next column,
but if the month has less than 31 days, the group header and one or more lines show up at the bottom of the page. 
I'm looking for a way to force the next column when Access finishes printing the last detail section of the Category group. I expect that this report will eventually have 3 or 4 categories.
As expected, Force new page after the group does not work.
The way the detail section is currently sized, if the month has 31 days, it wraps perfectly so that the next category start at the top of the next column,
but if the month has less than 31 days, the group header and one or more lines show up at the bottom of the page. 
I'm looking for a way to force the next column when Access finishes printing the last detail section of the Category group. I expect that this report will eventually have 3 or 4 categories.
As expected, Force new page after the group does not work.
Sheils
How about inserting a page break between the list and the category group. I take it that the category group is a subreport
ASKER
<I take it that the category group is a subreport >
No, the query for the report looks like:
SELECT Category, DocDate, Prod, Sales, [Sales]/NZ([Prod], 1) as Pct
FROM ...
And the report is Grouped by Category and then sorted by DocDate within category.
ReportDesign.jpg
No, the query for the report looks like:
SELECT Category, DocDate, Prod, Sales, [Sales]/NZ([Prod], 1) as Pct
FROM ...
And the report is Grouped by Category and then sorted by DocDate within category.
ReportDesign.jpg
ASKER
I will eventually place this inside a wrapper report that has a report header, page numbers, and date, but for now I'm just trying to figure out how to force the new column before starting the next category.
I see. It is bringing in the following months data when there is a short month.
How about creating a main form with recordsource=Select Format([DocDate],"yyyy/mm)
Then make the current form a subform linked to the mainform by the fldDate and insert a page break after the subform
How about creating a main form with recordsource=Select Format([DocDate],"yyyy/mm)
Then make the current form a subform linked to the mainform by the fldDate and insert a page break after the subform
Looks like I made an over site. It is the second column that is coming at the bottom of the first. I have never heard of column break in reports so I'd stick to the subreport approach. But this time I'd suggest two subreport side by side. One for stable and one for Category#.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
My last comment led me to modify the query to return 31 days of data regardless. Then, in the Detail sections Format event, I just set the visible property of the controls if the date field was not in the same month as the report.