Sort In Query

[zyus]

I have some problem to sort below field in my query.

RelMthYr: Format([fstrlsedt],"mmm-yyyy")

It turned out to be Apr-2009,Aug-2009,Jul-2009 etc....i'm looking for
Jan-2009,Feb-2009,Mar-2009 etc...

Thanks

 

[Stefan Hoffmann]

hi,

I have some problem to sort below field in my query.

RelMthYr: Format([fstrlsedt],"mmm-yyyy")

It turned out to be Apr-2009,Aug-2009,Jul-2009 etc....i'm looking for
Jan-2009,Feb-2009,Mar-2009 etc...

If your field [fstrlsedt] is a date/time field, then simply sort by the
field itself. Not by the result of the Format() function.


mfG
--> stefan <--

 

[Jeff Boyce]

If you want to sort by month, by the order in which they occur during the
year, you need to use the month's number, not its name.

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or psuedocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[Keven Denen]

I have some problem to sort below field in my query.

RelMthYr: Format([fstrlsedt],"mmm-yyyy")

It turned out to be Apr-2009,Aug-2009,Jul-2009 etc....i'm looking for
Jan-2009,Feb-2009,Mar-2009 etc...

Thanks

Here's a way to display the month name but sort by the order.

Keep the fieldRelMthYr as it is now, but don't sort by it. Add an
additional field MonthNumber: Month([fstrlsedt]). Uncheck the Show box
and use that to sort.

Keven

 

[John W. Vinson]

I have some problem to sort below field in my query.

RelMthYr: Format([fstrlsedt],"mmm-yyyy")

It turned out to be Apr-2009,Aug-2009,Jul-2009 etc....i'm looking for
Jan-2009,Feb-2009,Mar-2009 etc...

Thanks

If you want to sort chronologically, just sort by fstrlsedt. The Format()
function takes a date (which sorts chronologically) and converts it into a
text string (which sorts alphabetically).

I'd be inclined not to format the field in the query design at all; instead
just set the Format property of the Textbox on the form or report where the
data is being displayed.

Others have suggested using the Month() function to get a numeric month (which
will sort correctly); this is fine, but it will sort January 2010 before
December 2009 (or for that matter before March 1998).