[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 549
  • Last Modified:

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, 31 daysbut if the month has less than 31 days,  the group header and one or more lines show up at the bottom of the page.  28 days
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.
0
Dale Fye
Asked:
Dale Fye
  • 4
  • 3
1 Solution
 
SheilsCommented:
How about inserting a page break between the list and the category group. I take it that the category group is a subreport
0
 
Dale FyeAuthor Commented:
<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
0
 
Dale FyeAuthor Commented:
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.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
SheilsCommented:
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) As fldDate From ....

Then make the current form a subform linked to the mainform by the fldDate and insert a page break after the subform
0
 
SheilsCommented:
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#.

0
 
Dale FyeAuthor Commented:
SB9,

I've already got a 2 category version working, but my client has indicated that he anticipates that we will be adding at least 2 additional categories in the next couple of months.  I still think that wrapping the columns like this is the way to go.  I just need to figure out how to append a couple of blank rows at the bottom of each of the groups, so that the total number of lines in each group is 31.
0
 
Dale FyeAuthor Commented:
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.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now