Delet data from a field using a timer based on a date range

[Issachar5]

I would like to setup a code that would delete data from a table based on a
date range entered. Here is the scenerio, I have training database. Two
fields one for training hire date and the other trainer, I would like to put
in a time that says 90 days from hire date the trainer's name is deleted from
the trainer field. Is this possible. Please advise.

 

[Douglas J. Steele]

It's easy to create a query that can do that, but there's no real way to
make Access run the query automatically.

 

[John W. Vinson]

I would like to setup a code that would delete data from a table based on a
date range entered. Here is the scenerio, I have training database. Two
fields one for training hire date and the other trainer, I would like to put
in a time that says 90 days from hire date the trainer's name is deleted from
the trainer field. Is this possible. Please advise.

Ummm... why?

Even if she did the training 90 days ago, she still did that training; do you
want to make it impossible to later recall who did what training?

Sure, an update query can do this, but I'm perplexed at WHY you would want to
do so.

The query would be

UPDATE trainingtable
SET Trainer = Null
WHERE TrainingHireDate < DateAdd("d", -90, Date());

Run the query periodically (monthly say) to remove the old entries.

 

[John W. Vinson]

Or perhaps...

Now() doesn't return the current date; it returns the current date and time
accurate to the second. That's taking the "90 days ago" criterion very
literally, but yes, it will work. I'm a bit more comfortable using DateAdd()
because it doesn't make any assumptions about the engine's representation of
dates.

 

[David Kaye]

UPDATE trainingtable
SET Trainer = Null
WHERE TrainingHireDate < DateAdd("d", -90, Date());

Or perhaps...

 

[Risse]

I would like to setup a code that would delete data from a table based on a
date range entered. Here is the scenerio, I have training database. Two
fields one for training hire date and the other trainer, I would like to
put
in a time that says 90 days from hire date the trainer's name is deleted
from
the trainer field. Is this possible. Please advise.

 

[Paul Shapiro]

de noticias:[email protected]...

You could write a query that sets the trainerName to Null for any training
hire date more than 90 days ago, something like:
Update MyTable Set trainerName=Null Where trainingHireDate<Date()-90
You could add code to run that query automatically either every time the db
is opened, or every time a particular form is opened.

But it would seem better to keep the data by modifying the table structure.
For a training database, you might have tables like:
Courses (courseCode, courseTitle, trainingHours, description, etc.): A
training course which can be offered to students.
Instructor (instructorID, lastName, firstName, dateHired, etc.): A person
who can teach one or more courses.
Classes (courseCode (FK), classDateStart, instructorID (FK), etc.): An
offering of a training course.
Student (studentID, lastName, firstName, etc.): A person who can take a
class.
Enrollment (courseCode (FK), classDateStart (FK), studentID (FK), grade,
etc.): A Student taking a Class.

By separating the course and the class, you can keep all the instructor
records and keep all your training history.

 

[Om]

yah