Solved

Access: Sorting Months in a query in the correct order

Posted on 2007-11-27
13
1,664 Views
Last Modified: 2009-01-22
I have a query that sorts out months in the following format:

Field:              Month                
Row1         August 2006                        
Row2         September 2006            
Row3         October 2006                  
Row4         November 2006              
Row5         December 2006
Row6         January 2007
Row7         February 2007
Row8         March 2007
Row9         April 2007
Row10         May 2007
Row11         June 2007
Row12         July 2007
Row13         August 2007

The problem is that sometimes (Unlike above) the months are in the wrong order. How do I sort the month column to insure that the month and year are always in the correct order (As above)            
0
Comment
Question by:ouestque
  • 4
  • 3
  • 3
  • +3
13 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
what is the actual input field? is it datetime, or is it already a string in that format.
0
 
LVL 8

Accepted Solution

by:
Emil_Gray earned 500 total points
Comment Utility
If you are using a query then it is easy. Create a new field just for sorting purposes. Format your original date field there as follows:

Format([mydate], "yyyymmdd")
0
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
select * from NameofTable
order by format([Month],"yyyymm)
0
 
LVL 26

Expert Comment

by:jerryb30
Comment Utility
If text, Order By datevalue(MonthAndYear)
0
 
LVL 8

Expert Comment

by:Emil_Gray
Comment Utility
As I said initially since I presume you are using a query create a new field in the query. I'll call it myDate for the purpose of explaining.

Format the field as a Date/Time field. In the query the field would look like myDate: Format([MonthYear], "yyyymmdd") where [MonthYear] is the name of your field in the query that holds the data you are trying to sort. Then you sort the myDate field as you wish.
0
 
LVL 26

Expert Comment

by:jerryb30
Comment Utility
If it is a date field, it will order regardless of how you format.  
If it is a text field, you will not get format to handle it anyway.
0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
<If it is a text field, you will not get format to handle it anyway.>

why not try my post.

  select * from NameofTable
order by format([Month],"yyyymm)
0
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
?format("january 2007","yyyymm")  will give you

200701
0
 
LVL 26

Expert Comment

by:jerryb30
Comment Utility
I am amazed. I bow to your superior intellect. Yet again.
0
 
LVL 8

Expert Comment

by:Emil_Gray
Comment Utility
capricorn1 and jerryb30, if the field showing the Month and Year that the questioner provided is a Date/Time field then the solution I first proposed is the easiest answer. This is not rocket science.

Format a new field as a Date/Time field. In your query the field would look like;

myDate: Format([MonthYear], "yyyymmdd")

where [MonthYear] is the name of your field in the query that holds the data you are trying to sort. Then you sort the myDate field as you wish either Ascending or Descending.
0
 

Author Comment

by:ouestque
Comment Utility
Thank ya'll so much!!! I really appreciate you help. Emil_Gray posted the correct answer first and therefore she gets the points. Thanks!!!
0
 
LVL 8

Expert Comment

by:Emil_Gray
Comment Utility
ouestque, thank you. However I am a male not a female.

Emil Gray
0
 

Expert Comment

by:cspbarnes
Comment Utility
And if you looking to only do it for the month, let's say a Birthday list, then you would use
myDate: Format([Month], "mm")
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now