Ascending DATE order, help

  • Thread starter Thread starter mrg
  • Start date Start date
M

mrg

I'm trying to make a report sorted in date order, by month (ex. May 2006,
June 2006, July 2006). However, I can only figure out the obvious which is
alphabetically. How would I go about this for my report (or even for a query)
 
If you don't have the field set up as Date, then you won't be able to extract
the Month Number out of them I think.

Im not sure how I would go about this, it is possible to set up another
table that has all the months listed out like "January 2006", "February 2006"
and then a new column that numbers them from 1 to 12. Then you can create a
join in a query and add this new column and sort by it.
 
If the underlying table contains a column of date/time data type, YourDate
say, from which you are returning the month and year with
Format([YourDate],"mmmm yyyy") then add columns to the report's underlying
query by putting the following in the 'field' rows of two blank column:

SortYear: Year([YourDate])
SortMonth: Month([YourDate])

and sort the report first on SortYear and Then on SortDate.

If May 2006 etc are the actual values in a column of text data type,
MonthYear say, the add a column to the query:

SortYearMonth: Right([MonthYear]4) & Format(Month("1 " & [MonthYear]),"00")

and sort the report on the SortYearMonth column. Even if d mmmm yyyy is not
your usual long date format its internationally unambiguous so should work
regardless of the regional date settings in use by the system.

When using a query as the RecordSource for a report don't sort the query,
but use the report's internal sorting and grouping mechanism. The ORDER BY
clause of a query will usually be ignored; not invariably as is sometimes
said, but mostly, so can't be relied on to control the sort order of a report.

Ken Sheridan
Stafford, England
 
Back
Top