CODEWITHSUNDEEP
ms-accessdatabase-designms-access-2016
J. Cal
J. Cal

Reputation: 167

MS Access order table by order in which records were entered

I have a table in Access that I use as a progress tracker/to do list, with one field containing the date (short text) and what I did that day (long text). An example would be like this

date | progress
----------------
6/20 | did item1
     | tomorrow do item2 and item3
6/21 | long text I continue in the next line for visibilty
     | continued
     | to do tomorrow
6/22 | item6 completed

etc. I enter these things manually. the past 3 weeks or so that I have been updating this table, it opens in the same order every time -- the order in which I created the records. Recently, the table opened in a completely random order, and continues to open in that new order.

I know now that it would have been good to create an autonumber field and order it by that, or have the date field's default value =Now(). I ahve far to many fields to make a new ID field and manually number each record in the order I created them.

Is it at all possible to force the table to order the records in either the order I created the records, or at least the previous configuration (which was just ordered by the time created)?

Also, is there a better way to be doing this...? I want to just have a record for other people who will work with this in the future. In addition, I am new to SQL, and the Access SQl has weird/unique ways of doing things, so for some queries I know I may need in the future I keep a table with the query name and some documentation for what exactly it does and some notes about the syntax (the SQL editor does not allow for -- comments). Is there a better way to do this, too?

Thanks for any help!

Upvotes: 0

Views: 644

Answers (1)

Gustav
Gustav

Reputation: 55906

one field containing the date (short text)

Dates should never be stored as anything else than date values. No exceptions.

So change the data type of the field to Date, sort on this, and your troubles are gone.

To order Null dates last:

Order By Abs([DateField] Is Null), [DateField]

Upvotes: 1

Related Questions