We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

Implement Columns in Access report based on groups

Dale Fye
Dale Fye asked
on
Medium Priority
559 Views
Last Modified: 2013-11-28
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.
Comment
Watch Question

Commented:
How about inserting a page break between the list and the category group. I take it that the category group is a subreport
Dale FyeOwner, Dev-Soln LLC
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2010

Author

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
Dale FyeOwner, Dev-Soln LLC
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2010

Author

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.

Commented:
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

Commented:
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#.

Owner, Dev-Soln LLC
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2010
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview
Dale FyeOwner, Dev-Soln LLC
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2010

Author

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.
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a free trial preview!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.