Sorting Parts Of A Date

[CJ]

Is it possible to sort a date column by month then by year
in a query? I don't care about the day. What I need to
have happen is all of my records to sort in descending
order by year and month. Somewhere I saw that this is
possible by using left(2) or right(4) but I am not sure
of the full syntax. Can someone advise me on this?

 

[John Vinson]

Is it possible to sort a date column by month then by year
in a query? I don't care about the day. What I need to
have happen is all of my records to sort in descending
order by year and month. Somewhere I saw that this is
possible by using left(2) or right(4) but I am not sure
of the full syntax. Can someone advise me on this?

A Date is *not a text string*. It's actually stored as a floating
point number, a count of days and fractions of a day.

If you want to extract the year and the month from a date as integer
numbers, there are functions to do so: Year([datefield]) and
Month([datefield]) will return 2003 and 8 if passed today's date. If
you want to see record in order January (in any year, sorted by year),
then February and so on, just put two calculated fields in your query
using these functions and sort by them.

 

[CJ]

Unfortunatley the person that created this database to
begin with set this filed up as a text field so when I use
the functions you suggested I get #error#. Any
suggestions?

-----Original Message-----

Is it possible to sort a date column by month then by year
in a query? I don't care about the day. What I need to
have happen is all of my records to sort in descending
order by year and month. Somewhere I saw that this is
possible by using left(2) or right(4) but I am not sure
of the full syntax. Can someone advise me on this?

A Date is *not a text string*. It's actually stored as a floating
point number, a count of days and fractions of a day.

If you want to extract the year and the month from a date as integer
numbers, there are functions to do so: Year([datefield]) and
Month([datefield]) will return 2003 and 8 if passed today's date. If
you want to see record in order January (in any year, sorted by year),
then February and so on, just put two calculated fields in your query
using these functions and sort by them.


.

 

[John Spencer (MVP)]

One method would be to add a calculated column just for sorting:

Field: SortByDate: Format([YourDateField],"yyyymm")

In SQL that would be

SELECT ...
FROM TABLE
Order By Format([YourDateField],"yyyymm")

You could also use the Year and Month functions.
SELECT FROM TABLE
Order By Year([YourDateField]), Month([YourDateField])

 

[John Vinson]

Unfortunatley the person that created this database to
begin with set this filed up as a text field so when I use
the functions you suggested I get #error#. Any
suggestions?

Use the Left() and Mid() functions to pull the date apart. Since you
didn't say that it was a text field, and still have not said how the
text field is laid out, I can't be much more specific than that!

 

[CJ]

The date is displayed as follows:
11222003
So what I want to sort on is the first 2 digits and the
last four so how would I write this function?