sorting by month

[unknown]

I have my database and a field to enter months as a drop down box. It enters as the full word. I want them to be sorted so it goes January, Febuary, March etc in my report.

I was searching the database and found something with this function

DatePart("m",[Field Name])

If i knew how to use this it might help but i dont

 

[Jack Cowley]

You don't say where you are getting the names of the months, but you can create a table with two fields, mMonth and Sort. In the first field spell out the month (January) and in the Sort field put the number of the month (1 for January, 2 for February, etc.). Create a query based on the table and sort by the Sort column. Base the combo box on the query.

 

[unknown]

umm im not sure i follow, but the names of the months is from the months field which i use a look up wizard and typed in the names.
So i create anouther table just with the month names and the 1 for jan 2 for feb etc, relate them by the month and create a query from that to sort it?

 

[unknown]

i think i almost got it to work but it goes January october, november, december, feb, then its normal it does 1,10,11,12,2,3,4,5,6,7,8,9
err i dont know why its doing that

 

[Jack Cowley]

I assume then that you have a table with your data stored in it and one of the fields is a Date field and you want to sort this field by month so the months show up correctly in your combo box. To do this add a column to the query that is based on your table and put code like this in the top line:

sMonth: Month([NameOfDateFieldFromTheTable])

Set the Sort field to Ascending for this column. Now you months will be sorted Jan, Feb, Mar...etc... Base the combo box on this query.

I hope that I understand just what it is you are trying to do....

Jack

 

[Jack Cowley]

1, 10, 11, etc are text and not numbers. Change them to numbers and it will work.

Jack

 

[unknown]

i couldnt get the

sMonth: Month([NameOfDateFieldFromTheTable])

part to work so i gave up and just changed the text to number, i feel so stupid, i spent 2 hours tring to fix that and it was that easy

but i got it to work thanks

 

[Jack Cowley]

No need to feel stupid. Remember that Access is a very powerful program and it has a rather steep learning curve. Just hang in there and soon it will all make sense....

Good luck with your project.

Jack

 

[Tezcatlipoca]

i think i almost got it to work but it goes January october, november, december, feb, then its normal it does 1,10,11,12,2,3,4,5,6,7,8,9. err i dont know why its doing that

It's doing it because computers sort numeric data logically (so sorting goes 1, 1x, 2, 2x, 3, 3x, etc., where x is any amount of any digit(s)).

One way to solve it is to add a 0 to the front of your units, so, in your case, entering your data as 01, 02, 03, 04, 05, 06, 07, 08, 09, 10, 11, 12, etc. The computer will then group it properly. If you need your numbers to run into triple figures, then add 2 zeros otherwise your 100 figure will be sorted between the 10 and the 11.

 

[Jack Cowley]

Access does sort Numbers correctly. They only time that the Numbers are sorted 'incorrectly' is if they are saved as Text and not as Numbers. In the example provided by the original poster his numbers were saved as Text data types. Once the data type is changed to Numbers they will be sorted as we expect numbers to be sorted....

Jack