How to order a quey by month 'January..' 2

[delorfra]

When I want to display month ina query, it orders the month obtained by a format(mydate;"mm&quot command by alphabetical orders : April, August. How can I avoid this ?

Despite being a basic question i've never found how.

Thanks

 

[ghubbell]

Hello there,
Add another column to your query just like this:

MonthNo: DatePart("m",[mydate])

and in its sort row select descending. Should give you the results you want! Gord
ghubbell@total.net

 

[MichaelRed]

Gord,
How do you know they want it descending? I don't see that in the question?


MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[ghubbell]

A blunt assumption Michael. Gord
ghubbell@total.net

 

[ghubbell]

Delorfra,
Sort in any order you prefer. January to December would be Ascending, December to January would be Descending. Not Sorted will follow other sort orders in the query. You can also control your sorts by the position of the column in the query if you have other sorts too. Point is play around a bit and see all your options. Gord
ghubbell@total.net

 

[Quehay]

Hey there's another twist: The problem of a date range that extends from one year to the next. Date part "m" gives you something that sorts correctly within the current year, but if your date range extends from Oct of the previous year to Mar of the current you're out of luck. I've tried several approaches to this (admittedly not an exhaustive search yet) without success. Maybe DateSerial descending since that gives a truly linear, rather than cyclical, number to work with.

 

[MichaelRed]

Quehay,

Often a problem.

Try:
Format(DateField], "yyyy/mm&quot

Returns a clear and unambigious 'value' which may be used for sorting and grouping.
MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[skenglish]

I've been struggling with this same problem. I've already come up with the solutions proposed, but it only works if you display the months as numbers. When I display them as words, I'm back to the original problem, which is that the months are displayed in ALPHABETICAL order, NOT chronological order. I didn't see that problem answered here.

Any other suggestions?

Sandy

 

[MichaelRed]

You don't need to use the sorted value in hte display, just to Order it. build a related field with the text and use THAT one for the display.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[skenglish]

Thank you, Michael! That worked! Now that I think about it, it makes perfect sense.

Sandy