Automatically show previous dates.

[mbonning]

I am creating an Access 2007 datadase. I have a field labeled "Current date" where I would like to manually input a date. I have also created 10 Past date fields labeled "Past 1" through "Past 10". I would like these dates to automatically show the 10 most recent dates that I have entered in the "Current Date" field respectively. How do I make this happen? All fields are a Date/Time with a short date format.

[rpeare]

is the current date field a bound or unbound text box?

If it's unbound there's no real way to save the data unless you create a table specifically to store the dates you're looking up. Then you'd have to add add some sort of mechanism to remove the oldest date and add the newest date. I'm assuming you are doing this so you have some sort of record of what you have searched for or do you have some other purpose in mind?

[mbonning]

The "Current Date" field is bound. I am trying to show the last 10 most recent times that the "Current Date" field has been updated.

[rpeare]

Are you keeping a log of when a record is changed and the date it was changed? If you are updating the same record field as you go along there's no way to retrieve update information that I know of. If you want to be able to track who and when a record was changed you'd have to keep a physical log somewhere in your database.

If you don't plan on doing anything with the data other than having it available for review you may be able to use a multi value text box. Normally I would say never, never, never use them but if you don't intend to try and link any of the data in them and are strictly using them for reference it might be a solution to write the update person and date/time to a single field.

[orange]

It sounds like your data structure is n iot normalized, Can you show us picture of your tables and relationships?

[mbonning]

I have attached a copy of the database. All I am trying to do is keep a record of the 10 most recent times that I have performed a test. I want to manually input the "Current Test" date and have the database update all of the "Past" fields. Any ideas?

[rpeare]

This is a bad table design. Read about normalizing your database. Let's assume you have 3 different test types what you want to do is have a table that has your test types (with an unique identifier/autonumber field). Then a second table that contains it's on autonumber unique identifier, the testID from your first table and the test date.

Then you can use a SELECT TOP X query to return the x most recent records.