The autonumber field in my table keeps changing the order of my table. The autonumber seems to be tied to a date field I also have. It keeps defaulting to the wrong order. I want the first record to be the oldest date (i.e. 10/99) but it keeps picking the first record of 2003 as #1.
Autonumbers either increment, in which case sorting by them will present the data in the order it was entered, or they are random, in which case sorting by them will present the data in no particulary order. In either case, they bear no consistent and reliable relationship to your actual data, so you shouldn't be sorting by them anyway.
You can set the autonumber field as the primary key, but still set your data table's sort order to a different field (your data value, for instance), but you should really be applying the sorts on your queries, forms and reports since they might need to present the data in different ways.
By the way, you can set a table's sort order in the table's properties, or by sorting the opened table and then answering Yes when prompted to save changes.
I need to have a field automatically generate a number for a new record. This has worked for me in other databases but for some reason it won't work in this particular database. When a user needs to generate a new record, a number needs to be associated with it.
I assume your autonumber field is incrementing properly, but you don't
like the fact that it's determining the order of your records.
Have you tried setting the OrderBy property on your form or query to
order by date?
Just a thought....
Are you sure your date field in table is really in
date/time format, and not text? 10/98 in a text field is greater than