Sorting of dates no working?

[Buakaw]

Hi, I have a query that lists 3 columns:

Name, Amount Paid, Date

I sort by Name first, then by Date (Formatted as "mmm yyyy") but the results do not display the records sorted according to date as I expected.

This is the SQL code:

SELECT [tblMainStudentInfo].[First Name] & " " & [tblMainStudentInfo].[Last Name] AS Name, tblFeesPaid.AmountPaid, Format([MonthPaid],"mmm yyyy") AS MonthYear
FROM tblMainStudentInfo INNER JOIN tblFeesPaid ON tblMainStudentInfo.PrimaryKey = tblFeesPaid.fkStudentID
ORDER BY [tblMainStudentInfo].[First Name] & " " & [tblMainStudentInfo].[Last Name], Format([MonthPaid],"mmm yyyy");

The results show:
Clare Chang $180.00 Apr 2011
Clare Chang $180.00 Feb 2011
Clare Chang $180.00 Jan 2011
Clare Chang $180.00 Mar 2011

I expected records to be sorted as:
Jan 2011
Feb 2011


Mar 2011
Apr 2011

Anyone knows why? Thanks.

[William McKinley]

It's sorting in alphabetical order based on the Month name. You could include a field showing the numerical month and sort on that.

[Buakaw]

Ah..thanks, got it. I'm using this query to create a crosstab query, in order to make a report.

I would like the report to be sorted according to the month/year but I would like the display to be Jan 2011 instead of 1/1/2011.

Is there a way to accomplish this? Thank you.

[William McKinley]

Ah..thanks, got it. I'm using this query to create a crosstab query, in order to make a report.

I would like the report to be sorted according to the month/year but I would like the display to be Jan 2011 instead of 1/1/2011.

Is there a way to accomplish this? Thank you.

Include both the numerical month format and the MMM format. Display the MMM format, but hide and sort the report by the numerical format.

[thhui]

The MonthPaid field should be a datefield so that it would be sorted by date correctly.
The display could be formated in the query window's field column's property format field instead.
For example change it to "mmm yyyy"

Then it will be sorted under date order and displayed in "mmm yyyy" format.

[William McKinley]

The MonthPaid field should be a datefield so that it would be sorted by date correctly.
The display could be formated in the query window's field column's property format field instead.
For example change it to "mmm yyyy"

Then it will be sorted under date order and displayed in "mmm yyyy" format.

You learn something new every day!

[Buakaw]

The MonthPaid field should be a datefield so that it would be sorted by date correctly.
The display could be formated in the query window's field column's property format field instead.
For example change it to "mmm yyyy"

Then it will be sorted under date order and displayed in "mmm yyyy" format.

Hi thhui,
Thanks very much! What you described works perfectly for a normal query. However, how do you do this in a crosstab query? I cannot find a way to format this column in Properties when it's a crosstab query. Thanks!

[lvh519]

Include both the numerical month format and the MMM format. Display the MMM format, but hide and sort the report by the numerical format.

**deleted question** I figured it out. Thanks.