How to Sort Months from January-December

[Luke]

I have a database that is almost complete and I don't have time to make any drastic changes because I am leaving the country soon. I have a table, "tblEntity" with a field, "FiscalYearEnd". This field contains all of the months in a year using a drop down box so that it would be easier to choose a company's fiscal year end in a form or query. I did this in the Lookup tab in design view of "tblEntity". The "FiscalYearEnd" field has the following:

• Display Control: combobox
• Row Source Type: Value List
• Row Source: "January";"February";"March";"April";"May";"June"; "July";"August";"September";"October";"November";" December"

That's how I made the drop down box and it's been great because my boss doesn't have to type in each company's month.

I made a query and report based on each company's fiscal year end, which works fine. I grouped the report by each month, which also works.
HERE'S MY PROBLEM: I can only sort each month in either Ascending order (April, August, December, February, etc.) or Descending order (September, October, November, May, etc.).

I want to be able to sort the months in calendar order from January, Feb, March, April May and so on. I have tried a few different things but the only things I can think of are:

1. Creating an entirely new table with the Month and an Autonumber from 1-12 (months) Which is something I DO NOT want to do because then I will have to redo all of my reports, queries, relationships, and forms.
2. Using some sort of expression in a query
3. Using some sort of Input Mask or Validation Rule in the Design of my "tblEntity"

Does anybody have any suggestions as to how I can do this?



Thanks a lot.

-Luke

[orange]

I'd go with a table with
MonthNum (number) and
MonthName (string).

If you keep what you have for existing forms etc,
use the table to populate the combo using the number field to get Month by Month number.

Perhaps there's more to the issue, but that would seem feasible.

[rpeare]

The only thing I can think to do would be to add a 'sort order' calculated field to your query then you would have to build a formula that correctly calculated the sort order based on the fiscal year end. It would be pretty big formula and may become cumbersome, especially considering you don't want to add a table (which would likely be the easiest way). And you wouldn't use the autonumber field you'd use just a regular number field which you could then modify based on the end month of the fiscal year before you ran your report

What I would likely do is this:

Have a table

FYEnd    FYMonth  FYSort
January  January  12
January  February 1
January  March    2
...
February January  11
February February 12
February March    1
....
etc.

Then you would link this table to your data through the FYEND field and it would give you the sort order you wanted. This is the quickest solution to your problem.

[Luke]

Thanks, if I did that though I would have to create a relationship between the new table with the old one right? Also what do you mean by "(string)" for Monthname? Thanks.

-Luke

[Luke]

The only thing I can think to do would be to add a 'sort order' calculated field to your query then you would have to build a formula that correctly calculated the sort order based on the fiscal year end. It would be pretty big formula and may become cumbersome, especially considering you don't want to add a table (which would likely be the easiest way). And you wouldn't use the autonumber field you'd use just a regular number field which you could then modify based on the end month of the fiscal year before you ran your report

What I would likely do is this:

Have a table

FYEnd    FYMonth  FYSort
January  January  12
January  February 1
January  March    2
...
February January  11
February February 12
February March    1
....
etc.

Then you would link this table to your data through the FYEND field and it would give you the sort order you wanted. This is the quickest solution to your problem.

It sounds like you're much more familiar with access than I am. Ha I'm pretty much completely lost on what you're talking about but thank you for the advice. If I were to make an entirely new table with the Month and Number, how could I incorporate it without having to remake all of my forms queries and reports? Thanks a lot.

-Luke

[rpeare]

You couldn't do it without modifying any of your queries or reports, but you can't do that now anyway. One way or another you will have to change the code on your queries to get the sort to come out correctly. I'm offering a solution that's much easier to implement quickly rather than having to stumble through building a formula that will correctly calculate a sort order.

My table is basically building a matrix for every possible combination of month and fiscal year end so the table would have 144 rows. Then in your existing queries you'd just have to add this one table, link it from your existing FYEND field to the FYEND table in my example add the 'sort order' field and then perform your sort on that sort order field within your query.

Let me just say that regardless of what you do, you *will* have to change all of your existing queries and reports where you want this sorting to occur, there's no way around it.

[Luke]

Alright thanks a lot man, I'll look into it and try to figure it out.

[Luke]

What if I made an entirely new table called "tblFiscalYear" and included an Autonumber primary key field called "FiscalID" and a text field called "FiscalYearEnd". Then if I added a new field to "tblEntity" called "FiscalID" with a Number format, and made a relationship between both "FiscalID" fields, wouldn't it be possible to coorelate them somehow? Just thinking out loud.

[rpeare]

Not through relationships no. The only the a relationship does is establish links between tables so that when you write a query and start adding tables the links are already established for you. It has nothing to do with how the data is sorted (which is what you're after)

[randman1]

Try this. Create a new Public function in a standard module that converts the month string to an integer.

Public Function ConvMonth(sMonth As String) As Integer
    If sMonth = "January" Then ConvMonth = 1
    ElseIf sMonth = "Feburary" Then ConvMonth = 2
    ElseIf sMonth = "March" Then ConvMonth = 3
    ElseIf sMonth = "April" Then ConvMonth = 4
    '...
End Function

You can call this from a query or use as a sort/group expression and sort by the integer rather than the string.

[rpeare]

A good solution, but also requires changing all existing queries/reports

[randman1]

I think the OP would have to change either the reports or the queries if they are stored, rather than both. I can't think of a way to accomplish the goal without some modifications.

[rpeare]

Exactly right, it depends on how the reports are set up as to whether he needs to change them or not (when I put queries/reports I meant either or) but the primary issue is changing the queries so that you have something to sort on.