Sort months logically and not alphabetically

[Greek]

I have a minor dilemma I need help with. I have a report (based on a query)which informs staff of students who withdrew from our school, and the month and date they withdrew (sorted ascending). I am grouping by month and it looks like the attachment I've included.

It works well enough, but what I would really like is for the month column (Month([withdrawldate]) to show January for 1, February for 2, and so on, but I find that when I do that, the months sort alphabetically, and not logically. Would someone tell me the way to accomplish that in my query. Thanks very much.

 

[Frothingslosh]

Are you storing month and date separately? You shouldn't; just have the underlying query sort by date, and have the control on the report format the date to show it the way you want.

 

[plog]

You posted in the query forum, but listed your report, so I'm confused as to where you actually want this to happen. You can order a query (Using an ORDER BY clause) and you can order a report (using the Sort option) even if that report is based on a query that uses a different ordering method.

My advice for both instances is to add a field for the Month name in your query, using the MonthName function (http://www.techonthenet.com/access/functions/date/monthname.php):

WithdrawlMonthName: MonthName(Month([withdrawldate]))

Keep the month number, because that is what you will be ordering your query by. Then on your report, you show the WithdrawlMonthName field, but sort the report by the month number field.

 

[Mile-O]

My advice for both instances is to add a field for the Month name in your query, using the MonthName function (http://www.techonthenet.com/access/functions/date/monthname.php):

WithdrawlMonthName: MonthName(Month([withdrawldate]))

I'm with Frothingslosh here. No need for an extra function. Format([WithdrawalDate], "mmmm") would do what's needed display-wise.

 

[gemma-the-husky]

ignore this. didn't add much

 

[Greek]

I added the MonthName function and I am attaching my query design which runs fine. However, when I use this query to make a report, and group on month, it continues to sort alphabetically. There is something I am not understanding or communicating correctly, and I apologize for that. But, thanks to everyone thus far for helping me.

 

[plog]

First, "Month" is a poor choice for a field name because its a reserved word. I suggest you rename it by add a description of what the month represents (e.g. WithdrawlMonth).

Second, where'd your numeric month field go? That's the one you need to sort by.

 

[Greek]

Thanks, plog! I'll make the adjustments and try again. Andy

 

[Greek]

Something is wrong in this query, because when I create a report based on it, I still get the alpha sort in column one (feb, jan, mar), and that's with a sort on my number field. Please see attached. And, thanks again. A

 

[plog]

The query looks good. That sorts correctly right?

Can you post your database?

 

[Greek]

I believe it sorted correctly. The attachment shows how it ran. I will post a mini version of my db tomorrow, showing the same data. Thanks for all your help, pog. Andy

 

[Greek]

I'm posting a mini version of my problem, but with the same elements. There is a student table, and a query. From that query I have been trying to create a report showing student withdrawals, grouped by month and in each grouping showing the students and date or withdrawal (sorted ascending). Thanks, pog.

A

 

[Mile-O]

Is this what you are trying to do?

Also, I renamed Students to tblStudents (good practice, that!). And made a Forename/Surname field for your Students. Otherwise, what do you do when you get two Smiths?

But, then, two John Smiths....?

 

[Greek]

I am trying to use the query to create a report showing students withdrawal dates grouped by month of year, but, whereas the query shows the months in the proper order, my report doesn't do that. It sorts alphabetically and not logically (jan, feb, mar). Thanks for your help and suggestions. I typically use the tbl, frm, qry, naming conventions. I was just in a hurry to post this. Thanks again. A

 

[plog]

You need to add a sort option to the report using the month number (or even withdrawl date).

In design view of your report, click on the 'Group & Sort' in the ribbon. A menu will appear at the bottom, there you can click on the 'Add a sort' and sort your data by MonthNumber. Remember though, sorting options occur in the order in which you preference them. If you first want to sort by MonthNumber it must be at the top of the sort list.

 

[Greek]

Please look at my report. I did what you said and sorted on MonthofYear in report design view and still get an Alpha sort on WithdrawalMonth in column one. Thanks again. A

 

[plog]

Weird, it seems the way you Group items also effects the sorting. Your sorting on MonthofYear needs to be above the grouping. In design view, move the sort of MonthOfYear to the very top of the menu option for Grouping and sorting.

 

[Greek]

That did it! Thanks so much. All the best! A

 

[Greek]

Thanks, pog! The rearrangement of the group/sort order did the trick, and the report looks great. I appreciate all your help. A