Missing Months in Access Report Headings

Today I made a simple report in my Access database, with a list of articles that I’ve written in the past 60 days. I tried to group the articles by month, and that’s when the trouble started. My Access report headings didn’t show all the months, and it took me a while to solve the problem. Maybe this fix will help you too.

The Old Access Report

I already had a list of articles by date, and figured it would just take a couple of minutes to make a new version, with the list divided into months. Ha!

Here is the old report – just a report title and a list – no subheadings.

accessreportgroupdate01

Starting the New Report

The first few steps in the new report went well –

  • Made a copy of the old report, with a new name.
  • Created a revised copy of the report’s query, to show 60 days only
  • In the query, added a new field to show the first day of the month, based on the article date

Actually, if took me a couple of tries to get that date field added. At first, I used Date (like the Excel function), instead of DateSerial, which works in Access:

  • ArtYrMth: DateSerial(Year([ArtDate]), Month([ArtDate]), 1)

When working on queries, I love that Zoom window in Access – why can’t we have one in Excel? You can select a nice big font size, instead of squinting at an 8-point font formula.

accessreportgroupdate02

Testing the New Query

After revising the query, I ran it, to make sure everything looked okay. It was doing exactly what I needed it to do.

accessreportgroupdate03

Add Month Grouping to Report

Now I just needed to create Access report headings to show the months. So, back in the report design, I clicked the Add a Group button, and selected my new field (ArtYrMth) from the pop up list.

accessreportgroupdate04

Next, I used the Move Up button, to move the ArtYrMth group to the top of the list of grouping and sorting items.

accessreportgroupdate05

Then, to put the months in descending order, I changed the order to “from newest to oldest”.

accessreportgroupdate06

Add Header Text for Grouped Month

I wanted the month names in the group heading, so I added the ArtYrMth field in that header section, and formatted it as mmm yyyy.

accessreportgroupdate07

Then I clicked the View Report button – ready to admire my completed work. And this is what the report looked like. The July heading was missing!

Missing Months in Access Report Headings

Start the Troubleshooting

I couldn’t see any obvious problems in the report design window, so I checked the query again. Everything looked fine there too. All the July articles showed July 1 in the ArtYrMth field.

accessreportgroupdate03

More Troubleshooting

So, it had only taken me a few minutes to create the new report, but then I spent an embarrassing amount of time, trying to figure out why the July headings were missing. I can’t remember all the things that I checked/tried, but I left no troubleshooting stone unturned!

Finally, I decided to try the Article Date field as a grouped heading. What would happen with that? And that gave me the clue that I needed to solve my missing header problem.

I’ve outlined the trouble section in the screen shot below.

accessreportgroupdate09

Default Date Grouping By Quarter

Argh! When I grouped the Article date, the grouping bar showed that it would be grouped “by quarter”. Why is that the default? Did I miss that when setting up the ArtYrMth grouping?

I went back and checked the ArtYrMth grouping bar, but there was no mention of grouping by quarter. The bar just showed the sorting option, and an innocent-looking “More” button.

accessreportgroupdate10

When I clicked the More button, all the secrets were revealed. There was the “by quarter” setting – I clicked it and selected “by month” instead.

accessreportgroupdate11

Access Report Headings Are Fixed

I removed the ArtDate group (that I had used for troubleshooting), and viewed the report again. Lovely! There was the Access report heading for July, where it should have been all along.

accessreportgroupdate12

Final Tweaks

There were a couple of little tweaks to make to the formatting though. I went back into the Report Design, and got rid of that annoying shading on alternate headings, by deleting the “Darker 5%” option.

Also, I changed the Month headings to left alignment, because that’s where they should be!

accessreportgroupdate13

The Finished Report By Month

It took much longer than I expected, but here is the finished report, with a lovely formatted heading for each month.

The good news is that if (when?) this Access report headings problem ever happens to me again, I’ll have this article to help me figure out the problem in just a couple of minutes.

accessreportgroupdate14

______________

Save

Save

Save

This entry was posted in Microsoft Office Tips. Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *