Months sorting alphabetically instead of chronologically

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a report from a query that gives me dollar totals of shipments by
Vendor and Part Number per month. The date in the query is set as follows:
"Format$([ReportSubVendorDollars].[ShipDate],'mmmm yyyy')". This gives me
the results I want but in the report it is not being seen as a date so is
sorting alphabetically instead of chronologically. How can I get the months
in order instead of alphabetically?
 
Add two calculated fields to your query, in this order:

YearNum: Year([ReportSubVendorDollars].[ShipDate])
MonthNum: Month([ReportSubVendorDollars].[ShipDate])

Sort on these two fields.
 
Actually, if you're wanting the report to sort this way, no need to put
these fields in the query. You can add them to the Sorting & Grouping list,
which is what the report will use for sorting any way.

View | Sorting & Grouping

Add these two expressions to the list there:

Year([ReportSubVendorDollars].[ShipDate])
Month([ReportSubVendorDollars].[ShipDate])

Set both for ascending sort.

--

Ken Snell
<MS ACCESS MVP>


Ken Snell said:
Add two calculated fields to your query, in this order:

YearNum: Year([ReportSubVendorDollars].[ShipDate])
MonthNum: Month([ReportSubVendorDollars].[ShipDate])

Sort on these two fields.

--

Ken Snell
<MS ACCESS MVP>

neenmarie said:
I have a report from a query that gives me dollar totals of shipments by
Vendor and Part Number per month. The date in the query is set as follows:
"Format$([ReportSubVendorDollars].[ShipDate],'mmmm yyyy')". This gives me
the results I want but in the report it is not being seen as a date so is
sorting alphabetically instead of chronologically. How can I get the months
in order instead of alphabetically?
Click to expand...
Click to expand...
 
Back
Top