Sorting and grouping

[julostarr]

I'm using Access 2003. I have been entering orders with dates, but in order
to have the orders separated by month for a report I added a field in my
table that displays just the month that the order is to be shipped. I
created this with a numeric value at the beginning to make it easier to enter
the data, but it also displays the month name along with it so it will
display on the report that way. For example:

1 - JAN
2 - FEB

The problem is that now it sorts the numbers in the wrong order in both my
report and quiery. Like this...

1 - JAN
10 - OCT
11 - NOV
12 - DEC
2 - FEB

How can I fix this?

 

[KARL DEWEY]

You have a text field and it is doing a text sort - 11 before 2.
Use a number field just for sorting and another text field for display.

 

[julostarr]

How do I set that up?

You have a text field and it is doing a text sort - 11 before 2.
Use a number field just for sorting and another text field for display.

 

[John Spencer]

Alternative
Change the text field so you are storing two character month numbers
01- Jan
02- Feb
etc.

If you do that you will get the desired sort order.

The better solution is - don't store anything other than the date. You can
always calculate the month number and the month name as needed

Month(YourDate) will return a number from 1 to 12
Format(YourDate,"mm") will return a string from "01" to "12"
Format(YourDate,"mmm") will return a 3-letter string for the month name
Format(YourDate,"mm - mmm") will return a string with 2-digit month and
3-character name string

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[julostarr]

Thank you for your help!

Just so you know I created this field because I have different columns for
date. This is because I my company wants to keep track of ship date changes.
With this column I can change it to reflect the changes in ship month other
wise I would have just sort by the original date.