Solved Group report not displaying correctly

[taball0829]

As you can see from the picture, the months and days of the birthdays and anniversaries are sorting and displaying correctly, but the group header is not displaying the appropriate month. Initially, I had names in the list without dates, so the first header displayed nothing, so I checked for, and eliminated null dates in the underlying query. Before doing that, the only difference I saw was that the blank header, with all the names of those who had no dates, were listed in alphabetical order. I had blank header, December, then January repeated for the rest of the report. Now the blank header is gone, but...

Any clues?

 

[Minty]

What is the SQL for the report record source query?

 

[CJ_London]

sounds like you are trying to sort on the month name rather than the month number

 

[taball0829]

What is the SQL for the report record source query?

SELECT Contacts.birth_anniversary, DatePart('m',[birth_anniversary]) AS sort_month, Contacts.last_name, Contacts.family_id, Contacts.ID, Contacts.first_name, Contacts.email, Contacts.job_title, Contacts.phone_one, Contacts.phone_two, Contacts.phone_three, Contacts.phone_fax, Contacts.Address, Contacts.City, Contacts.state_province, Contacts.zip_post_code, Contacts.country_region, Contacts.web_page, Contacts.Notes, Contacts.Category, Contacts.last_name_suffix, Contacts.owns_phone_one, Contacts.owns_phone_two, Contacts.owns_phone_three, DatePart('d',[birth_anniversary]) AS sort_day, Trim$([first_name] & " " & [last_name] & [last_name_suffix]) AS display_name
FROM Contacts
WHERE (((Contacts.birth_anniversary) Is Not Null) AND ((Contacts.last_name) Not Like "*&*"))
ORDER BY DatePart('m',[birth_anniversary]), DatePart('d',[birth_anniversary]);

 

[taball0829]

sounds like you are trying to sort on the month name rather than the month number

I have the Format for sort_month as "mmmm" for the Report, for display purposes. It is numeric in the table and the query. I changed it to "m" to try your suggestion. The results were: 12, 1, 1, 1, etc. to the end of the report.

In case you read this and assume I have sort_month in the table, I do not. The date in the table is numeric, and the DatePart in the query is numeric.

 

[Minty]

Okay, that's a bit weird.

The sort order in your query has no effect on the way the report orders things by the way.
Make sure you apply sorting in the report, otherwise, the order isn't guaranteed.

If that doesn't solve it ( and i doubt it makes any difference) any chance you could upload a small sample of data and the report in a stripped-down DB ?

 

[taball0829]

I think it would be good to know why this works the way it does, but it now works correctly. I made a display_month in my underlying query with MonthName function, and put that field in my Group Header section to replace sort_month field that I had before. Thank you, Minty and CJ_London for your inputs!!!

 

[taball0829]

Okay, that's a bit weird.

The sort order in your query has no effect on the way the report orders things by the way.
Make sure you apply sorting in the report, otherwise, the order isn't guaranteed.

If that doesn't solve it ( and i doubt it makes any difference) any chance you could upload a small sample of data and the report in a stripped-down DB ?

Thank you. I could have used that advice earlier! I had a couple other fields sorted in the query earlier, and the report was really whacked then. I had a birthday from January, followed by an anniversary in September, and on and on. Did not seem to be to have any sort order whatsoever.

 

[CJ_London]

It is numeric in the table and the query - so to be clear you are sorting on the numeric value in the report?

And I don't think you can format as you are - see this example fDatePart is what you are bringing through in your query as sort_month, fpDatePart is using the format property, ffDatePart is using the format function (not clear which you are using in the report)

Think you need to bring both values through in your query - sort the numeric value in your report, display the second value

sorted here

 

[Minty]

I think it would be good to know why this works the way it does, but it now works correctly. I made a display_month in my underlying query with MonthName function, and put that field in my Group Header section to replace sort_month field that I had before. Thank you, Minty and CJ_London for your inputs!!!

The reason for Reports ignoring sort orders is a relatively simple one.

Because you can set up grouping in reports even from a non-grouped source Access effectively has a completely separate "hidden" query that the report uses, based on your original source query or table.

It then adjusts this to suit the grouping and ordering you apply in the report to get the output desired.