sorting dates by month

S

Sanford

I am stymied when date sorting. Access sorts the date column by the year. I
want it to sort by month. Years are added by default from the form that
builds the Access file. How can I change the sorting to month?
I am not a programmer and have limited Access experience.
 
K

Ken Sheridan

Firstly is the column a true date/time data type? Secondly, what exactly do
you mean by 'sort by month'? At face value that would mean all rows for
January for any year come first, followed by all rows for February for any
year etc. If you wanted that you can return the month, as a number, from any
date with the Month function, so can sort on this:

SELECT *
FROM MyTable
ORDER BY MONTH(MyDate);

More likely is that you'd want to sort by Year then by Month within each
year. Not surprisingly there is a Year function too;

SELECT *
FROM MyTable
ORDER BY YEAR(MyDate), MONTH(MyDate);

This will sort in ascending year/month order but not by date within each
month. To do that simply sort on the date column:

SELECT *
FROM MyTable
ORDER BY MyDate;

By default sorting is in ascending order. To sort in descending order, i.e
the latest dates first add the DESC keyword:

SELECT *
FROM MyTable
ORDER BY MyDate DESC;

I suspect I might be missing something between the lines in your problem, so
if this doesn't answer your question post back with more details.
 
J

James A. Fortune

Sanford said:
I am stymied when date sorting. Access sorts the date column by the year. I
want it to sort by month. Years are added by default from the form that
builds the Access file. How can I change the sorting to month?
I am not a programmer and have limited Access experience.
Click to expand...

tblMonthsToSort
DatesToSort Date/Time Format m/yy
10/06
5/06
9/05
10/05
9/06
12/05
1/06
2/06
5/06
11/05
9/05
8/06

qrySortedMonths
SELECT DatesToSort FROM tblMonthsToSort ORDER BY
Format([DatesToSort],'yymm');

9/05
9/05
10/05
11/05
12/05
1/06
2/06
5/06
5/06
8/06
9/06
10/06

James A. Fortune
 
S

Sanford

Thank you for your reply Ken.
The column is formatted mm/dd/yyyy in calendar format. 'Sort by month' means
that I can create a report where any month's dates are sorted by date value
regardless of year.
But what does your response mean??
 
K

Ken Sheridan

SELECT is an SQL keyword. SQL is the language in which queries are written.
If you design a query in the query designer in Access the SQL code is created
for you. You can see it by switching to SQL view. To sort by date within
each month, regardless of year, one way is to sort on the return value of the
MONTH function. If you then have a second sort level on the date itself the
rows will be sorted by month, then each group of rows in the same month,
regardless of year, will be in actual date order:

SELECT *
FROM MyTable
ORDER BY MONTH(MyDate), MyDate;

As you want to sort in a report, however, its pointless doing it in the
query. Instead you should do it in the report via the sorting and grouping
dialogue in report design view. To do this you can base the report on a
query which includes a computed column to return the month values from each
date:

SELECT *, MONTH(MyDate) As SortByMe
FROM MyTable;

To do this in query design view rather than SQL, design the query in the
usual way, then in the 'field' row of a blank column in the query design grid
put:

SortByMe:MONTH([MyDate])

substituting the real name of your date field of course. Make sure the
'Show' checkbox is checked.

In a report with this query as its RecordSource open the sorting and
grouping dialogue in design view, either from the toolbar or the View|Sorting
and Grouping menu item. Select SortByMe as the first sort level and MyDate
as the second.

BTW the format of a date/time column in datasheet view of your table is
immaterial. Date/Time values are actually stored as a 64 bit floating point
number as an offset from midnight on 30 December 1899, the integer part of
the number representing days, the fractional part the time of day. The
format is just how you see them. Here I use the UK format of dd/mm/yyyy, but
the above would still work as the actual values of the dates are the same
regardless of the format employed.
 
G

Guate

Hi Ken,

I had posted a similar question to this one over the weekend, and was having
difficulty understanding the replies I was getting, since I wasn't familiar
with the SQL view. Your explanation about doing it in design view was
exactly what I needed to finally figure it out--thanks! For us novice types,
it's perhaps easier to explain these things in design view first, then we can
look to see what it looks like in SQL view! The problem I was running in to
was that I already had a sorted field in my query, so I wasn't sure where to
place the new SQL statement and kept getting error messages. Now that I have
it working in design view, I can clearly see what the SQL view was supposed
to look like. Again, many thanks! Guate


Ken Sheridan said:
SELECT is an SQL keyword. SQL is the language in which queries are written.
If you design a query in the query designer in Access the SQL code is created
for you. You can see it by switching to SQL view. To sort by date within
each month, regardless of year, one way is to sort on the return value of the
MONTH function. If you then have a second sort level on the date itself the
rows will be sorted by month, then each group of rows in the same month,
regardless of year, will be in actual date order:

SELECT *
FROM MyTable
ORDER BY MONTH(MyDate), MyDate;

As you want to sort in a report, however, its pointless doing it in the
query. Instead you should do it in the report via the sorting and grouping
dialogue in report design view. To do this you can base the report on a
query which includes a computed column to return the month values from each
date:

SELECT *, MONTH(MyDate) As SortByMe
FROM MyTable;

To do this in query design view rather than SQL, design the query in the
usual way, then in the 'field' row of a blank column in the query design grid
put:

SortByMe:MONTH([MyDate])

substituting the real name of your date field of course. Make sure the
'Show' checkbox is checked.

In a report with this query as its RecordSource open the sorting and
grouping dialogue in design view, either from the toolbar or the View|Sorting
and Grouping menu item. Select SortByMe as the first sort level and MyDate
as the second.

BTW the format of a date/time column in datasheet view of your table is
immaterial. Date/Time values are actually stored as a 64 bit floating point
number as an offset from midnight on 30 December 1899, the integer part of
the number representing days, the fractional part the time of day. The
format is just how you see them. Here I use the UK format of dd/mm/yyyy, but
the above would still work as the actual values of the dates are the same
regardless of the format employed.

Sanford said:
Thank you for your reply Ken.
The column is formatted mm/dd/yyyy in calendar format. 'Sort by month' means
that I can create a report where any month's dates are sorted by date value
regardless of year.
But what does your response mean??
Click to expand...
Click to expand...
 
Top