How to Arrange Table Data into Date Order

[hcoffin1941]

I have an Access table with transactions that were entered in random order. I need to recreate the table with those transactions entered in date order. I started by making a copy of the table (table structure only) then creating an Append Query sorted by the date field thinking that the records would be appended in the order created by the query. It did not work as I wanted. My next technique was to use VBA code to open a recordset that was created with the following SQL statement:

strSQL = "SELECT * FROM tablename ORDER BY datefield" & ";"
Set rst1 = db.OpenRecordset(strSQL)

The compile hung up, apparently not liking something about the SQL statement. I have done these things many times before during the last 15 years of using Access. I am about to shoot my computer.

Any ideas about what I am doing wrong?

 

[WayneRyan]

h,

The data in an Access table is NEVER guaranteed to be in any order.

When you view the data on a form, report or query just sort the data by the date field or whatever.

Don't waste your time trying to "store it" in any order.

Wayne

 

[hcoffin1941]

Thanks for taking the time to reply.

Ok, I understand that access does not store the data in any particular order, but I still need my data in the table to appear in date sequence. The table contains a primary index field that contains a unique number starting with 1. This field is an AUTONUMBER data type. If the data that is entered is displayed in order of this index field. I am certain that you already know this.

My second attempt to accomplish my objective was to create a new table by copying the original table and saving it (Table Structure Only) to a new table. Next I tried to use VBA code to read the contents of the original table in date sequence by creating a Recordset defined by the following SQL Statement:

strSQL = "SELECT * FROM tablename ORDER BY" & datefieldname & ";"
Set rst1 = db.OpenRecordset(strSQL)

The compiler choked on that.

If that code works, I would follow it by adding each record in the Recordset to my new table. The first new record should have an indexfield value of 1. The second record processed in this manner would have an indexfield value of 2 etc etc. The new table would end up with all of the records displayed in indexfield value order; 1,2,3 etc.

My problem is that the SQL statements above do not work. That is what I need help with. Any ideas would be appreciated.

Thanks,

 

[JHB]

I think it is because you need a space between ORDER BY and the value coming from datefieldname
You code line is:

strSQL = "SELECT * FROM tablename ORDER BY" & datefieldname & ";"

With adding the space it looks like below:

strSQL = "SELECT * FROM tablename ORDER B[B][COLOR=Red]Y "[/COLOR][/B] & datefieldname & ";"

A good info to get is the error number and error description instead of, "The compiler choked on that."
Can't you use a append query or a make table query, instead of a recordset it is very much faster?

 

[Galaxiom]

No need for recordsets. Use an Insert (AKA Append) query to copy the data into the new table. If the source data is sorted then it should be written to the new table in the same order.