Delet data from a field using a timer based on a date range
in [General]
|
Prev: Access 2003 Text Box
Next: Switchboard warning
From: Issachar5 on 1 Apr 2010 15:34 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.
From: Douglas J. Steele on 1 Apr 2010 15:47 It's easy to create a query that can do that, but there's no real way to make Access run the query automatically. -- Doug Steele, Microsoft Access MVP http://www.AccessMVP.com/DJSteele (no e-mails, please!) "Issachar5" <Issachar5(a)discussions.microsoft.com> wrote in message news:C2CA9BBC-A1B9-4871-9B1C-1731F3190C32(a)microsoft.com... >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.
From: John W. Vinson on 1 Apr 2010 16:44 On Thu, 1 Apr 2010 12:34:01 -0700, Issachar5 <Issachar5(a)discussions.microsoft.com> wrote: >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 [MVP]
From: David Kaye on 1 Apr 2010 21:00 Please.Reply(a)To.The.Newsgroup wrote: >UPDATE trainingtable >SET Trainer = Null >WHERE TrainingHireDate < DateAdd("d", -90, Date()); Or perhaps... >UPDATE trainingtable >SET Trainer = Null >WHERE TrainingHireDate < NOW() - 90
From: John W. Vinson on 1 Apr 2010 20:06
On Fri, 02 Apr 2010 01:00:52 GMT, sfdavidkaye2(a)yahoo.com (David Kaye) wrote: >Please.Reply(a)To.The.Newsgroup wrote: > >>UPDATE trainingtable >>SET Trainer = Null >>WHERE TrainingHireDate < DateAdd("d", -90, Date()); > >Or perhaps... > >>UPDATE trainingtable >>SET Trainer = Null >>WHERE TrainingHireDate < NOW() - 90 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. -- John W. Vinson [MVP] |