Hello All,

I have a query which runs, and i extract the "month and date" from a field using this function:

MonthYear: Format([dateContractEnds],"mmmm yyyy")
(ascending)

When i run the query, it works great, and the dates are sorted as they are supposed to be (I.E.// Jun 2010 first, August 2010 next, Jan 2011 next, and so on in order of smallest to greatest date).

However, i now created a report and using the "sorting and grouping" option on this field (MonthYear). It now mixes up all the dates and they aren't in the same order as the query.

Any rationale?

I basically want the report to be ordered by month and year...

Andrew.

Have you got Grouping in place?

Are you sure that works in the query? I just tested, and it did exactly what I expected, which was April, then August, etc. In other words, alphabetically, not by date. I'd do this for sorting:

Format([dateContractEnds],"yyyymm")

vbaInet, this time I get to bust you for not reading. ;)

Hi Pbaldy,

Yes - my query definately returned the right information in the query itself, but the report woudl be sorted alphatecically. I tried your suggestion, and it seemed to do the trick...
Funny!

Thanks,
Andrew.

vbaInet, this time I get to bust you for not reading. ;):D That's what happens when I'm trying to multitask.

Andrew: Glad it worked. I can't imagine how that could have sorted correctly in the query. You're sure there wasn't another sort on a real date field?

vbaInet: You can multitask?!? I'm jealous. :p

It wouldn't have sorted correctly because the month names aren't even alphabetical. If you want an alphabetical sort then you need to use a combination of Month() and Year() (I would imagine)

Paul: I try, but you've seen the result of trying :p