How to stop auto sorting in access tables

[nebula53]

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

[liimra]

Solution/

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

[nebula53]

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

[Delerna]

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

[nebula53]

yes its like this
but i need it to be displayed in the same way I entered it without sorting it in this way

[liimra]

Solution/

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

[NeoPa]

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.

[Delerna]

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 alphanumericall y 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

[nebula53]

Thank you Delerna and NeoPa for your advice I really appreciate it . but Liimra solution have solved my problem.

[NeoPa]

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 :)