Ordering month field chronologically

[Shannon]

I have a Month field that I would like to sort
chronologically instead of alphabetically. I created a
MonthNum field which would store "1-12" based on the month
entered in the Month field, and plan to sort the
report/form by that field. Does anyone have suggestions
for automatically populating the MonthNum field based on
the Month value? Or perhaps a better suggestion for
sorting Month chronologically.

Thanks much.
Shannon

 

[Duane Hookom]

What values are you actually storing? If you are not storing a full date,
you should be storing a month number. It is very easy to display the month
name if you have the month number.

 

[shannon]

I am storing the month name for each record...this is the
value the user wants to see. If I store the month number,
how do I display the month name? And can I use month
number without having user intervention on that field?
Thanks.
Shannon

 

[Duane Hookom]

You can use a combo box on your forms for entering the month number. Set the
Row source Type: Value list
Number of Columns: 2
Bound Column: 1
Row Source: 1,January,2,February,3,March,....12,December
Column Widths: 0;1
Then your users will only see the month name but the month number will be
stored. Storing a month name makes way too much work.

If you can't change your field to a numeric then you can create an
expression that returns the month number:
Month(CDate([MonthName] & " 1, 2000"))