469,336 Members | 5,527 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,336 developers. It's quick & easy.

How to stop auto sorting in access tables

I have combo box includes the dates per month starting from 2009 to 2015

January 2009
February 2009
>
>
>

The table is sorted automatically by access and the months are scrambled . how I can keep the same order when I inter the data
Aug 21 '10 #1

✓ answered by liimra

Before we move into solution, there is something imp. about programs (access, excel..). They sort information either in Alphabetical way when we talk about “text” or in Numerical way when we talk about numbers; Not to mention that dates are considered to be numbers. You are inputting dates (month and year) as text there instead of numerals and that is why Access will sort it in that way.

Anyways, here is the solution.
You just enter dates like this (Excel can fill it for you easily):
01/01/2009
01/02/2009
31/12/2009




31/12/2015

And in the date format (open table which stores the values, select datasheet - format use this without quotes “mmmm yyyy”. Finished you are done.
I have attached a database with the solution just mentioned.

More Info:
If you use (all without quotes” ”mm yyyy” then Access will read January 2010 as “01 2010” and if you use “mmm yyyy”, Access will read same date as “Jan 2010” but when you use “mmmm yyyy” Access will read it the way you want.

Regards,
Ali

9 11090
liimra
119 100+
I Understand that you want to sort them in Ascending order (Jan Feb Mar..etc).

Select the combo box in design view and in its property sheet - under "Data" ---> click on the Row Source and then the built query will open . From there you can choose the sorting you want for the field/column you want. It might be very useful also to create independent query for that Combo box as it will be optimal if you want to use access on network.

why don't you just sort that column in table datasheet view and then save changes

Regards,
Ali
Aug 22 '10 #2
unfortunately it’s not working , my data in the Combo Box appeared like this

January 2009
February 2009
March 2009
January 2010
February 2010
March 2010

And when I save it its get scrambled
Aug 23 '10 #3
Delerna
1,134 Expert 1GB
when you say scrambled is it
April 2009
April 2010
April 2011
April 2012
April 2013
April 2014
April 2015
August 2009
August 2010
...
....
etc

???

Which isn't scrambled, it is sorted alpha-numerically
Aug 23 '10 #4
yes its like this
but i need it to be displayed in the same way I entered it without sorting it in this way
Aug 23 '10 #5
liimra
119 100+
Before we move into solution, there is something imp. about programs (access, excel..). They sort information either in Alphabetical way when we talk about “text” or in Numerical way when we talk about numbers; Not to mention that dates are considered to be numbers. You are inputting dates (month and year) as text there instead of numerals and that is why Access will sort it in that way.

Anyways, here is the solution.
You just enter dates like this (Excel can fill it for you easily):
01/01/2009
01/02/2009
31/12/2009




31/12/2015

And in the date format (open table which stores the values, select datasheet - format use this without quotes “mmmm yyyy”. Finished you are done.
I have attached a database with the solution just mentioned.

More Info:
If you use (all without quotes” ”mm yyyy” then Access will read January 2010 as “01 2010” and if you use “mmm yyyy”, Access will read same date as “Jan 2010” but when you use “mmmm yyyy” Access will read it the way you want.

Regards,
Ali
Attached Files
File Type: zip month.zip (19.1 KB, 176 views)
Aug 23 '10 #6
NeoPa
32,182 Expert Mod 16PB
You need to sort the values by the dates they represent rather than the alpha representation (the strings shown).

If you need help with this then you will need to share where you are getting the data from and how you are formulating it for the control.
Aug 23 '10 #7
Delerna
1,134 Expert 1GB
You also need to explain this statement from your first post more fully.

The table is sorted automatically by access
It seems you are talking about the sorting in the table being scrambled, not in the combo box.
My instinct tells me that you might have the field storing your month/year string set as a key field and this might explain the alpha numeric order of the records in the table, whereas you want the records to remain in the order they were entered.

Is that correct?




I don't have a full understanding of how it works in access (my DB engine of choice nowadays is MS SQL Server) but when you make a field in a table a key field an index is automatically created for that field to help make queries run faster when you use that field in a WHERE clause.

One type of index causes the records in a table to automatically be sorted alphanumerically like in a dictionary. It is that sorting that provides the speed improvement when querying that table.

I am not sure I am on the right track for your problem here as you don't give us much detail to go on



And limra is correct, if you want dates to sort as dates then you probably should save them as dates and not strings.

You could also save them as numbers in YYYYMM format
Aug 23 '10 #8
Thank you Delerna and NeoPa for your advice I really appreciate it . but Liimra solution have solved my problem.
Aug 24 '10 #9
NeoPa
32,182 Expert Mod 16PB
Thanks for posting Nebula.

I think Ali's suggestion is on pretty much the right lines and I'm not surprised it helped you resolve your problem. You should probably try to be clearer when asking questions though. It makes it easier for us to answer, and means that you get better answers (ones that fit your question more accurately), more quickly. Benefits all round.

Anyway, I'm pleased you're able to go on now :)
Aug 24 '10 #10

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

10 posts views Thread by Job Lot | last post: by
5 posts views Thread by Santiago Ordax Solivellas | last post: by
3 posts views Thread by Random Person | last post: by
46 posts views Thread by Adam Turner via AccessMonster.com | last post: by
reply views Thread by | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
1 post views Thread by Marylou17 | last post: by
1 post views Thread by Marylou17 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.