Sorting a date field on Year and Month

[Guest]

This simple thing is driving me crazy.
I have a date field called "Divedate". All i want is Group and Sort on the
Year and month. The grouping works (i use format(divedate,'yyyy mmm')). But
the sorting is alphabetical. So i end up with my resulting query issorted
like this:
2005 dec
2005 nov
2005 Oct
2006 feb
2006 march
2006 jan
I've tried date serial, datepart, date everything!
All i want is the query to show everything in the calendar order. I am not
using the query in a report, just printing the query.
Thank you

 

[Rick B]

Change your group and sort to Format(divedate,'yyyy mm')

You can still display the format you listed in your group header or footer.

 

[Rick Brandt]

This simple thing is driving me crazy.
I have a date field called "Divedate". All i want is Group and Sort
on the Year and month. The grouping works (i use
format(divedate,'yyyy mmm')). But the sorting is alphabetical. So i
end up with my resulting query issorted like this:
2005 dec
2005 nov
2005 Oct
2006 feb
2006 march
2006 jan
I've tried date serial, datepart, date everything!
All i want is the query to show everything in the calendar order. I
am not using the query in a report, just printing the query.
Thank you

Format() returns a string so it will osrt as a string. Have a second
calculated field of...

Format(divedate,'yyyymm')

Show the one you have now, but sort on this one above without showing it.

 

[Tom Ellison]

Dear Sylvie:

The dates would be in order if sorted by the year and month, but only if the
month by which they sort is a number.

For the grouping you could use either. But for the sorting use:

Format(YourDate, "yyyy mm")

This puts things in order by year and month. I used this specifically
because it would be compatible with your Grouping, although it would have to
be substituted at certain places, though not necessarily for the display,
which could remain mmm.

Tom Ellison

 

[jeff.j.ku]

Is the field formatted as a date? Access should recognize the field as
a date field and then transform your date into a MS date code from
which you can sort.

To change the field, go to the table with your and into the design.
Under the date field, make sure that the format is date/time. If you
did not enter the date appropriately, you MAY lose the date information
for each of your records. If that is the case, you may have to parse
your date field and combine it in a new field for sorting.

 

[Guest]

Thank you Rick but i am not sure what you mean by "change your group".

 

[Guest]

Yes, the field i am grouping on and extracting the year and month is the Date
field.
All dates fonctions work. It is just sorting on format(mydate,'yyyy mmm')
that doesn't sor the way i need. It sorts by alpha on month instead of
calendar.
But someone suggested i tried yyyy mm instead so i will try it - but i've
been wasting so much time on this that now i am behing on my work.

 

[Guest]

Thank you Tom. I haven't had a chance to try your suggestion. BUt i shall
very soon. I am not sure what you mean by "For the grouping you could use
either".
I group on all the fields, how could I not. When you select Group in access
isn't for all the fields ?

 

[Tom Ellison]

Dear Sylvie:

For simplicity, I'll take it back. Group by both ways of formatting it.

Tom Ellison