Months of the year in chronological order

drisconsult

Drisconsult
Local time
Today, 14:09
Joined
Mar 31, 2004
Messages
125
Hello All

Does any know how to index months of the year in chronological order, such as

Jan
Feb
Mar

Etc.

Regards
Terence
Nairobi
 
Create a new fld in the query using datepart() pulling out the month in numerical format then sort on that fld. If Needed, you can also pull out the month in "JAN" format for display use...
 
Don't format your date before you sort the data. Format the date in the control on the report or form.
 
Pat Hartman said:
Don't format your date before you sort the data. Format the date in the control on the report or form.

Hum...

Don't format your date you wish to display before you sort the data. :)
 
Listing dates in chronological order

Hello KenHigg and Pat Hartman

Many thanks for taking the trouble over my problem.

I am using a combo box to display the months:

January
February
March

etc.

And then highligting the required month, then the day in another combo box, then the the type of over time payment in another combo box. This is then calculated in a text field. All is working nicely

Howerver, I have to display all months worked by each individual in a report. Is it still possible to use the Datepart() or "JAN" format display in a Report using a text field?

Regards
Terence
Mombasa 2.42pm Saturday 10 September

I am including a screenshot of the form that creates all the data.
 
Many people format dates in their queries:
Select fld1, fld2, Format(myDate, "mmmm/yy") as formDate
From ...
Where ...
Order by Format(myDate, "mmmm/yy");

They then don't understand why the records appear in alpha order -
April
August
December
February
January
June
July
March
May
November
October
September

If you simply sort on the original, unformatted date field, the rows are returned in the expected order.

Select fld1, fld2, Format(myDate, "mmmm/yy") as formDate
From ...
Where ...
Order by myDate;
January
February
etc.

The difference is the original date field is actually a number with each succeeding day having a serial number one higher than the previous day. When you format a date, you turn it into a text field and if you sort on a text field, text rules apply. That means that April is the first record you'll see and September is the last because that is how the alphabet works.
 
Last edited:
Hello Pat

Many thanks for your help here but I am still having a problem. The field I am trying to sort on is called CMONTH. After allocating a month, date, and type of overtime to a Commander (of an armoured car) I am trying to list all his overtime earned in chronological order in a Report, it isn't necessary but would be nice if I could achieve it.

Are you advocating the SQL code that you have outlined at the query stage or the report stage. Forgive me I am trying to get to a level I haven't been before.

Regards
Terence
Mombasa
Sunday 11 September @ 5pm.
 
The report should use a query as its RecordSource. For forms, you use an Order by in the query itself. For reports, you use the report's sorting and grouping properties and NO order by in the query.

You haven't said what kind of field CMONTH is. If it is a text month, you'll need to add a numeric translation to get the correct sort. You can use Choose() to convert the text to a number that will sort correctly. If it is a real date/time field, sorting on it will produce correct results.
 
Months in chronological order

Hello Pat

Sorry about the error. The CMONTH is a text field. I use a combo box to list the months of the year. Included is a screenshot of the combo box in question.

Can't thank you enough for taking the trouble to sort me out (no pun intended)

Regards
Terence
Mombasa
 

Attachments

  • FORMS045.JPG
    FORMS045.JPG
    61.4 KB · Views: 379
Terence,

Display what you currently do, but sort on this:

cdate(Mid(CMonth, Instr(1, CMonth, " ") + 1))

Essentially given "Monday 12 September 2005"

Convert the "12 September 2005" to a date field. Access
will sort it just fine.

Wayne
 
Change the RowSource of the combo to:
1,"January",2,"February", 3, "March" ....etc.
this will give you a numeric value followed by a text value.
Change the column count to 2.
Change the bound column to 1.
Change the column widths to 0", 1"
This will give you a combo with two columns, the first of which is numeric and hidden.
 
Months in chronological order

Hello Wayne

It was very kind of you to take the trouble over my problem. I have tried your line of code and am still having problems.

I have included two screenshots, one of the query, the other the result of the query in the report. As you can see my months are not in chronological order.

Regards
Terence
Mombasa
 

Attachments

  • FORMS047.JPG
    FORMS047.JPG
    30.4 KB · Views: 451
  • FORMS048.JPG
    FORMS048.JPG
    32.3 KB · Views: 387
Terence,

Based on your screenshots; you're not sorting by anything!

Try making a new column in a query with the CDATE(Mid...) from the
earlier post. Do you see dates?

You need to ORDER BY the new CDATE column.

Wayne
 
Looks like Wayne and I are viewing your problem differently (I don't see a CDate field. I only see CMonth and CDay) but the bottom line is - date data should ALWAYS be stored in a field with a date data type. It should NEVER be stored piecemeal in text fields. It is nothing but trouble to try to get a text date back to a number so that it can be properly sorted. So, until you can get it into a numeric value, you will never be able to sort it.
 
Pat,

He has a "CMonth" field --> "Monday 12 September 2005"

I thought the easiest approach was to make a new column in the
query:

NewField: CDate(Mid([ThatLongDateField] ...

Just using all but the "Monday " stuff.

If he sorts on the converted date he should be fine. BUT I don't
see that in the attached PICs.

Your approach works fine too (Gee that's a surprise!). :p

Where are we anyway? I'm using smileys!

Wayne
 
Months in chronological ordert

Gentleman

I am very impressed with your responses to my problem. Can't thank you both enough. But I have a dilemma. How can I show months of the year in anything but a text format?

I include a screenshot of the culprit in question. The first combo box allows the user to allocate a month (text field). The second combo box allows the user to allocate a day in the month (numeric field 01 to 31). The third combo box allows the user to allocate the number of overtime hours worked (numeric field 1 to 4 hours).

The bottom three fields are text fields that calculate the hours worked.

Thanking you both for taking the trouble to help a moderate user come to terms with reality.

Terence
Mombasa
Tuesday 13 September.
 

Attachments

  • FORMS050.JPG
    FORMS050.JPG
    31.3 KB · Views: 158
Terence,

Is your current problem in the report or with the combo boxes?
 
Months in chronological order

Hello Pat

Many thanks again for your help, but I feel that I must give up on this one as I am taking up far too much of your time.

I tried looking at the combo box rowsource code that you suggest but cannot find a place to insert your suggestion i.e. 1, January etc.

Included is a screenshot showing the present rowsource code of the combo box. The table is called tblMonths.

Regards
Terence
 

Attachments

  • FORMS051.JPG
    FORMS051.JPG
    48.7 KB · Views: 174
Ok. So you have a table with employee overtime info. In the table, when a person works overtime, you store the month in one fld as text. Say 'January', "February", etc. Then you store the day in a numeric field, say 1, 2, etc. and you also store the amount of overtime in a numeric field.

Now in your report, sayfor a given person, you want to total up the over-time by month. The problem is that the month's don't appear in chronological order. Is all this correct or close enough to correct?
 

Users who are viewing this thread

Back
Top Bottom