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.
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.
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.
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.
Next, I used the Move Up button, to move the ArtYrMth group to the top of the list of grouping and sorting items.
Then, to put the months in descending order, I changed the order to “from newest to oldest”.
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.
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!
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.
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.
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.
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.
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.
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!
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.
______________