Forum Home
Press F1
Thread ID: 127062	2012-10-02 09:49:00	MS Access - Change Report Layout	WarNox (8772)	Press F1

Post ID	Timestamp	Content	User
1304677	2012-10-02 09:49:00	Hey, I'm trying to change the report layout to 'block' manually because if I do it via the Report Wizard it creates other issues. Basically the report attached is the way I want it except the extra spaces which are not necessary, see attached. Does anyone know of an easy way to fix this? 4342 Thanks for any help.	WarNox (8772)
1304678	2012-10-02 21:20:00	I suspect you've got Group Header sections for each of Date, Category, SubCategory. In the report design, simply cut the box for Date and paste it to the same position in the Subcategory Header. Repeat for Category. The Headers for Date and Category will now be empty. Size them vertically to 0 height. For both the Date and Category fields you may want to set the Hide Duplicates Property to Yes. Fingers crossed that the job is done.	Paul.Cov (425)
1304679	2012-10-02 23:38:00	Hi Paul, Yes, that's what I thought but then it seems to skip out some fields, see attached. Design view as it was: 4344 New design view: 4345 New result: 4346	WarNox (8772)
1304680	2012-10-03 03:29:00	Yeah, you moved the fields up instead of down. Take all three - Date, Category, Sub Category - and place them into the Sub Category Header. Otherwise you are bang on the money I believe.	Paul.Cov (425)
1304681	2012-10-03 04:18:00	I see, misread that. Yes, now it looks mostly right but this comes back to the problem I had with the default 'block' layout. Duplicates aren't fully hidden. I've set Hide Duplicates on Date, Category and also tried Sub-Category but it never hides all the duplicates. Yet another screenshot attached and as you can see 'May 2012' shows up multiple times. 4348 Thanks again.	WarNox (8772)
1304682	2012-10-03 07:33:00	Yeah, looking back at the Design View, you've got the Date field grouping on individual dates (in the sorting and grouping bit), and then using a function in your Text Box to chop it down to just the month and year. While the formatted output of "August 2012" looks the same each time, the source info for each of those entries may be 01 Aug 2012, 02 Aug 2012, 03 Aug 2012, so the report spits out a fresh line each time. To get around this you need to go back to the Sorting And Grouping section and put the date formatting into there. Just replace the Date entry with the same formatting you've used in the Text Box for Date, being =format([Date],"MMM YYYY") or something similar. Job done?	Paul.Cov (425)
1304683	2012-10-03 07:39:00	I don't believe you need the "$" in the Format function either, but dunno the significance of being with vs without it.	Paul.Cov (425)
1304684	2012-10-03 08:46:00	This report was originally done by the wizard and I've just been editing it :) So the Date thing (=Format$([Date],"mmmm yyyy",0,0)) was automatic. Sorry I'm not sure what you mean by the Sorting and Grouping section? Below you can see the Group/Sort section... 4349	WarNox (8772)
1304685	2012-10-04 09:01:00	They may have renamed it in a newer version of Access, or hidden it somewhere under that damned ribbon somewhere. Anyway, on an old version of Access there's a bunch of buttons along the top (when in design view) which give Field List, Properties, Toolbox, Sorting and Grouping. So in the old days (my days) it was a button up there which opened the box with sections that you needed. My turn with a pic. The bits in red being something like what you're looking for. Hmm, haven't done a pic here before... hope it works.... clear as mud.	Paul.Cov (425)
1304686	2012-10-05 09:35:00	I think the Sorting and Grouping part is now at the bottom under 'Group, Sort and Total' but it's rather confusing. I figured if I move the 'Date' field back up to it's own category it comes close enough. I can live with 1 line of empty space per month :) Thanks for all the help!	WarNox (8772)
1 2 3