Headings for Snaking Columns (Access 2000)

Topic

New Reply

I have a 2 column report with snaking columns. The column headings are in the page header, they duplicate for each column and span the width of the page. That part works fine. However, sometimes there is only 1 column of data for the report. In that case the 2nd column heading set is printed with nothing beneath it. How can I print the 2nd column heading set conditionally; that is only if there is data for the 2nd column of the report?

Thank you.

Reply | Quote

Replies

Create a new header for your detail information (Under View Menu, sorting and grouping, whatever your detail section is set Group header to Yes). Put your column headings in that header instead of the page header. In the new header, set the Force New Page property to “before section”.

You will only need column headings for the first row and they should take up no more space than the width of one column. They will repeat only if the column snakes.

In the page setup dialogue, have 2 columns and set your column layout to down, then across. I also always set the printer for this type of report to “Use specific printer” then any columns or particularly small margin settings aren’t lost.

I think I got all of the pertinent information. If it doesn’t work, let me know and I can upload a copy of a report I did recently that does just this and you can check the properties. The important bits are to get that stuff out of the page header, have the before section property set and to have the columns set properly.

Peter N

Reply | Quote

Thank you for your reply. After following your directions the column headings do print for only the left hand column. However, the Force New Page Before Section setting on the Detail Header is causing a new page for every detail record. I needed the page to fill and the columns to snake, not a new page for each record.

I’m still lost on this and I would appreciate an example.

Reply | Quote

Here is a little example. To see the snaking without adding in extra data, go into page setup and make the bottom page margin 5 or 6 inches. This is a quick, really stripped down version of a report I am currently using, so table and query design is more complicated than it might otherwise be as I didn’t want to start from scratch.

I would suggest you look first in sorting and grouping and have it set the way I have it in the envelope header. Also notice the macro in the page footer to reset the page number if you want that.

If you still can’t get it to work, post yours and I’ll look at it or one of those nice people who really know what they are doing will.

Peter

Reply | Quote

It occurred to me as I was posting the other database, that you may have a report more like this example here, where you want all of the data from a particular table and you haven’t got any grouping levels at all.

In that case, I believe the only way to deal with the problem is through code and conditional formatting of the labels for the second column. Essentially the label would be visible if there was data in the cloumn and not visible if there wasn’t. Unfortunately, that is beyond my ability to help. Perhaps someone else can.

Peter

Reply | Quote

Nor one to give up on a challenge– assuming your problem is per my last post (you are showing all of the data in a table and not grouping on a specific field), here is a solution. I don’t think it is the most elegant, but if your database isn’t huge, it will work.

Add in a field to your table (called fakefield in my example) and populate it with an update query with the number 1. I set the field size to byte rather than long integer as it takes up less space and the point is to give you a field to sort on.

If your existing report is proving intransigent, you might want to rebuild it from scratch using the report wizard. Then go and adjust the columns, etc in page settings per my original post.

Hope this works.

Peter

Set up your report as mine is (pay particular attention to the Group Heading properties the Repeat Section field is vital and I forgot about it in my original post). When the report is working properly, set the fakefield field visible property to no and it will be hidden, but force your report to snake and your headings to do what you want.

Reply | Quote

Hi Peter,

Nice example! But I believe it can be even easier – you don’t need the fake field at all. You can set the “field” of the group header to an expression that returns a constant, for example =1, or =”Lounge”. Setting the Repeat Section property of the group header to Yes is enough to make it appear above used columns only. The advantage is that you don’t need to modify the table at all.

I have attached a modified version of your sample database.

Reply | Quote

Thanks Hans. I knew there was a simpler way to do it, but I have never seen the trick of setting a constant as the “field” in a group header. I also realized by the time I got to sample number three that the key to the whole thing was the repeat section property. Thanks for filling in the last pieces of the puzzle. I’ve saved your attachment for future reference.

Peter

Reply | Quote

Now that the project is delivered and the pressure is off, I have to admit that I do not understand why this works.
Can you please explain it to me?

Reply | Quote

Hi Thomas,

You wanted to have header text above the columns in use. Access reports have up to three kinds of headers:

• The report header. This is not suitable for your purpose because it is printed only once, at the start of the report.
• The page header. This is not suitable either, since it is fixed, without any tie to the data. You would need complicated code to accomplish your goal using the page header, if it is possible at all.
• Group headers. These have the advantage that they are tied to the data – a group header is only printed if there are records in the group. But – your report has no grouping… How to solve this? Well, create a dummy group. The “source” of a grouping in a report usually is a field, but – as witnessed by the Sorting and Grouping window, it can also be an expression. Since we really don’t have criteria to group on, we specify an expression that returns a constant value. I used a simple example: =1, but it might as well have been =”Woody” or =False. It doesn’t matter, as long as it’s constant.
  [/list]So we are going to use a dummy grouping. Normally, the group header will be displayed at the start of the group (which is the entire report in this case). But Access provides a nifty property RepeatSection. By default, it is False. But if you set it to True, the group header will be repeated at the top of the next page or column if the group doesn’t fit in the page or column. Precisely what we need – the header will be displayed as long as there are still records to go. If the last column in the report stays empty, there will be no group header above it.

Reply | Quote

Thank you Peter and Hans – finally snaking column headings that only print when the column has data!
I need to thank both of you, but I don’t want to clutter up the lounge, so hopefully you will both see this.
I worked through all the samples and got the desired results with the =1 Group Header and Repeat =Yes property.
I sincerely appreciate your efforts on this.

Reply | Quote