Report by Month Sorting Issue

[Tyler at Creme]

I have a monthly report that is sorted by monthly, but it is sorting Months
alphabetically, not Chronologically. I only see an option to Sort A to Z or
Z to A. How do I sort chronologically?

 

[Steve]

Add a calculated control to your recordsource:
=Month(MyDateField)

The calculated control will contain the integers 1 to 12 depending on your
date field. Sort on the calculayed field.

Steve
(e-mail address removed)

 

[John W. Vinson]

I have a monthly report that is sorted by monthly, but it is sorting Months
alphabetically, not Chronologically. I only see an option to Sort A to Z or
Z to A. How do I sort chronologically?

Sort by the date, or by Month([datefield]) - that will be chronological (or
numeric). You can display the month name while sorting by something else.

 

[John... Visio MVP]

I have a monthly report that is sorted by monthly, but it is sorting Months
alphabetically, not Chronologically. I only see an option to Sort A to Z
or
Z to A. How do I sort chronologically?

What does your report look like and how are you obtaining the field you are
sorting by? A little more detail and we can help you without adding any
extraneous fields to your tables or reports.

John... Visio MVP

 

[Tyler at Creme]

The field I am sorting by is a "report wizard-created" field created when I
wanted the report to be summarized by month. This field's record source is a
query field that is just standard mm/dd/yyyy dates.

The report is grouped by month, then by sites (numbered 1 thru 20) with
monthly averages and percentages for each site. Does this info help you
determine a more individualized solution to my problem?
Thanks

 

[John W. Vinson]

The field I am sorting by is a "report wizard-created" field created when I
wanted the report to be summarized by month. This field's record source is a
query field that is just standard mm/dd/yyyy dates.

The report is grouped by month, then by sites (numbered 1 thru 20) with
monthly averages and percentages for each site. Does this info help you
determine a more individualized solution to my problem?
Thanks

No.

Please open the report in design view. View its Properties; find the
"Recordsource" property. Click the ... icon by it and open the query in SQL
view and post the SQL text here.

Also post the fields in the report's Sorting and Grouping property.

 

[Tyler at Creme]

John please see the SQL below:

SELECT DISTINCTROW Weekly_Data_Query.School_Num,
Weekly_Data_Query.School_Name, Format$([Weekly_Data_Query].[Week_Ended],'mmmm
yyyy') AS [Week_Ended By Month], Sum(Weekly_Data_Query.Inquiries) AS [Sum Of
Inquiries], Round(Avg(Weekly_Data_Query.[Inquiries])) AS [Avg Of Inquiries],
Sum(Weekly_Data_Query.Tours) AS [Sum Of Tours],
Round(Avg(Weekly_Data_Query.Tours)) AS [Avg Of Tours],
Sum(Weekly_Data_Query.Enrollments) AS [Sum Of Enrollments],
Round(Avg(Weekly_Data_Query.Enrollments)) AS [Avg Of Enrollments],
Sum([Weekly_Data_Query]![Tours])/Sum([Weekly_Data_Query]![Inquiries]) AS
ItoT, Sum([Weekly_Data_Query]![Enrollments])/Sum([Weekly_Data_Query]![Tours])
AS TtoE,
Sum([Weekly_Data_Query]![Enrollments])/Sum([Weekly_Data_Query]![Inquiries])
AS ItoE
FROM Weekly_Data_Query
GROUP BY Weekly_Data_Query.School_Num, Weekly_Data_Query.School_Name,
Format$([Weekly_Data_Query].[Week_Ended],'mmmm yyyy'),
Year([Weekly_Data_Query].[Week_Ended])*12+DatePart('m',[Weekly_Data_Query].[Week_Ended])-1;

I could not find a "Sorting and Grouping" property in the same list that
included the "recordsource" property.
This is what is said when I right click to get to the report properties and
click on the "Sorting and Grouping" option right above properties:

Group on Week_Ended By Month with A on Top
Sort by School_Num

If this isn't what you are looking for, please tell me how to get to the
"Sorting and Grouping" property.

Thanks again for your help.