Order records so most recent entry to table appears first in linked form Access 2003

[Alby22]

I have a linked table between two databases(A & B). The data is entered by multiple users in to A once all the data is entered I review the data in B and then append that data to my main table in B. I'm essentially using the linked table as a buffer so if there are any entry mistakes they don't go directly into the main table. After the data is appended I would like to view it in the form that is linked to my main table, the problem is when I review the records they are all out of order although they are in order when I directly open the table. I was just wondering if there was an easy fix so when I open the form the most recent record by date appears first. The column/field is DATE I tried using the order by property(Order By: tablename.DATE) but that didn't seem to work. Any suggestions would be helpful.

Additional Info:I have previous and next record navigation buttons in the form. I would have no problem opening the table and looking at the data but I'm not the only person who will be accessing it so I'm just trying to make it as user friendly as possible.

[Rabbit]

The most likely reason that the order by isn't working is because your date field isn't actually stored as a date but as a text. Convert it to a date before doing the order by.

[twinnyfo]

Alby22,

Are you sure the Order By On Load property is also set to Yes? I have encountered such strange cases where this property is set to yes and still won't order properly. One solution, which I have found sometimes works when Access quirkily does something it shouldn't, is to remove TableName from the Record source completley, then reset the record source to TableName. For some reason it will re-order the data in the form based on the current settings of the table automatically. Again, a strange, quirky thing, but sometimes it works.

Best of luck!

[Alby22]

You are right it was a text field I should have checked that but I assumed it was set as a date. Now the problem is that to change it to a date it says that I have to delete the relationship it has with another table then change it and re-establish the relationship. Will changing it to a date have any major effects that you could predict on the data base. There are many other queries that use that filed through queries.

[twinnyfo]

First, good DB practices would prevent you using a date as a primary key or as a relationship within a table. Relationships between tables should typically be based on a Primary key in one table as a foreign key in another.

So, if your date is somehow in a relationship with another table, that should be fixed first.

Then, make sure all your datews are "dates" and not text, and re-establish any relationships between tables properly.

[Alby22]

Yeah the database is a mess and it is obvious whoever made it didn't really know what they were doing. Is there a way that I can just convert the text into a date/time in the order by statement? I don't wanna change much because some how it works and I'm afraid if i start changing things its going to bring on more troubles that frankly I don't have the time to deal with.

[twinnyfo]

The place to start is the format of the date to start with. What is the format, so we can work on converting it to a date? After this, just keep in mind that the recordsource for the form will have to be the query you make, and not the table itself., but good chance that all fields (except the date) will still be updateable.