Problem sorting date field & then indexing

[Splinters]

I need some help with a strange problem in a table of date & time stamps.

The table has the following fields (others left out for brevity):

Name Format
-----------------
intID Autonumber index
dteDate Date/Time
dteStart Date/Time

I needed to insert some records that were out of order, so I deleted the intID column, added them on the end of the table, ran an assending sort of the dteDate column, and everything was in the proper order.

I then recreated the intID index Autonumber field.

When I checked the table, the new records were at the bottom of the table again!

Here is where the old records meet the new records:

[FONT=Arial][SIZE=2][COLOR=#000000]intID[/COLOR][/SIZE][/FONT]   [FONT=Arial][SIZE=2][COLOR=#000000]dteDate[/COLOR][/SIZE][/FONT]    [FONT=Arial][SIZE=2][COLOR=#000000]  dteStart[/COLOR][/SIZE][/FONT]
[FONT=Arial][SIZE=2][COLOR=#000000]1040[/COLOR][/SIZE][/FONT]   [FONT=Arial][SIZE=2][COLOR=#000000]5/27/2010[/COLOR][/SIZE][/FONT]   [FONT=Arial][SIZE=2][COLOR=#000000]7:39:00 PM[/COLOR][/SIZE][/FONT][FONT=Arial][SIZE=2][COLOR=#000000]
1041[/COLOR][/SIZE][/FONT]   [FONT=Arial][SIZE=2][COLOR=#000000]5/28/2010[/COLOR][/SIZE][/FONT]   [FONT=Arial][SIZE=2][COLOR=#000000]7:50:00 PM[/COLOR][/SIZE][/FONT]     [FONT=Arial][SIZE=2][COLOR=#000000]
1042[/COLOR][/SIZE][/FONT]   [FONT=Arial][SIZE=2][COLOR=#000000]5/28/2010[/COLOR][/SIZE][/FONT]   [FONT=Arial][SIZE=2][COLOR=#000000]8:56:00 PM[/COLOR][/SIZE][/FONT]     [FONT=Arial][SIZE=2][COLOR=#000000]
1043[/COLOR][/SIZE][/FONT]  [FONT=Arial][SIZE=2][COLOR=#000000]10/14/2009[/COLOR][/SIZE][/FONT]   [FONT=Arial][SIZE=2][COLOR=#000000]2:21:00 PM[/COLOR][/SIZE][/FONT]     [FONT=Arial][SIZE=2][COLOR=#000000]
1044[/COLOR][/SIZE][/FONT]  [FONT=Arial][SIZE=2][COLOR=#000000]10/18/2009[/COLOR][/SIZE][/FONT]   [FONT=Arial][SIZE=2][COLOR=#000000]1:12:00 PM[/COLOR][/SIZE][/FONT]     [FONT=Arial][SIZE=2][COLOR=#000000]
1045[/COLOR][/SIZE][/FONT]  [FONT=Arial][SIZE=2][COLOR=#000000]10/18/2009[/COLOR][/SIZE][/FONT]   [FONT=Arial][SIZE=2][COLOR=#000000]6:09:00 PM[/COLOR][/SIZE][/FONT]     [FONT=Arial][SIZE=2][COLOR=#000000]
1046[/COLOR][/SIZE][/FONT]  [FONT=Arial][SIZE=2][COLOR=#000000]10/18/2009[/COLOR][/SIZE][/FONT]   [FONT=Arial][SIZE=2][COLOR=#000000]7:53:00 PM[/COLOR][/SIZE][/FONT]

As you can see, the 10/14/2009 record follows the 5/28/2010 record - which it shouldn't be doing...

I even tried making a copy table, copied the dte fields (without the index) into it, ran the sort and then created the index...same thing happened.

What am I missing here?

 

[pbaldy]

You shouldn't worry about the order of records in the table itself. Think of it as a bucket you just dump records into. You sort the records when you display them to the user in a form or report.

 

[gemma-the-husky]

and as far as access is concerned, this IS the case - any record in a table is equivalent to anyother, and therefore the sort order is not critical

so what access does when it adds autonumbers initially, is to process the records in the most logical manner for access - which probabaly means in the order you added them, since that is the order they will be on the disk - probably.

the thing is, DON'T try and use autonumbers for sorting purposes. They are only useful for managing links between this table and other tables in the database. If the date order matters, add ANOTHER INDEX, to sort the records by date.

and use a query rather than the table to view your records, as then you can impose a sort order.

 

[Splinters]

OK, thanks for the replies - for some reason I wasn't notified even though I have it flagged for instant notification...

I hadn't realized that I shouldn't use auto-number for an index...I'll try changing that.

 

[vbaInet]

I hadn't realized that I shouldn't use auto-number for an index...I'll try changing that.

Just wanted to bring to your attention what gemma-the-husky said regarding your reply:

the thing is, DON'T try and use autonumbers for sorting purposes. They are only useful for managing links between this table and other tables in the database. If the date order matters, add ANOTHER INDEX, to sort the records by date.

He didn't say you shouldn't use the Autonumber field for indexing, he said for sorting purposes, don't rely your sort on the AutoNumber field. I hope you haven't gone making drastic changes.