Results 1 to 12 of 12
  1. #1
    Join Date
    Jul 2003
    Posts
    292

    Unanswered: Deleting Records

    I have some records with dates on them.. Is there a way to delete them after five years... ?

    thanks

  2. #2
    Join Date
    Jan 2004
    Location
    Jersey, UK
    Posts
    108
    Not sure if you mean delete automatically after 5 years or if you just need to find those records older than 5 years.

    If it's the latter you could:

    1) Sort the field (click in the field and click the Z-A button) and look for the date manually then delete (or archive would be my preference... I rarely delete anything!).

    2) Create a query and in the criteria section of the date field enter < 06/01/99. This will display all results older than 5 years ago - you can delete/archive these.


    Regards - Andy

  3. #3
    Join Date
    Jul 2003
    Posts
    292
    Actually I meant automatically.. I do have dates set to records when users enter them in.. so when they enter in a record.. todays date is set to that record..

    If there is a way.. to do it automatically ?

    thanks for the response..

  4. #4
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Originally posted by TonyT
    Actually I meant automatically.. I do have dates set to records when users enter them in.. so when they enter in a record.. todays date is set to that record..

    If there is a way.. to do it automatically ?

    thanks for the response..
    Ok. HOW "automatic" are you looking for? There is no *POOF* here today and gone tomorrow capability ... You can setup either a schedule or a form that periodically runs (or wakes up) that does the deletion ...

  5. #5
    Join Date
    Jan 2004
    Location
    Jersey, UK
    Posts
    108
    Ditto M Owen.

    You could attach some code or query to a startup form in your db but once you've done a batch of archiving do you really want a reminder every time you open the db that x number of records are older than 60 months?!

    For the effort versus benefits involved/received, I'd sooner create a query and just run it as a quarterly process.


    Regards - Andy

  6. #6
    Join Date
    Jul 2003
    Posts
    292
    Seems like the quarterly suggestion would be the best bet.. Just how would I go at getting started at this..

    thanks for all your help

  7. #7
    Join Date
    Jan 2004
    Location
    Jersey, UK
    Posts
    108
    Hi

    Create a query and use this code in the date field where you want to find records that are older than 5 years:

    <DateAdd("yyyy",-5,Now())

    If you put that code into a delete query it will look something like this:

    DELETE Table1.Field1
    FROM Table1
    WHERE (((Table1.Field1)<DateAdd("yyyy",-5,Now())));


    Regards - Andy

  8. #8
    Join Date
    Jul 2003
    Posts
    292
    IS there a way to run the code.. once every year ?.. like on a specified date.. check to see if there are any records that are over five years old.. if there is.. then msgbox you have records over 5 years old.. would you like to delete.. ?

    something like that..


    again thanks

  9. #9
    Join Date
    Jan 2004
    Location
    Jersey, UK
    Posts
    108
    I've never tried but I suppose you could create a procedure to do this.

    Every time the db opens or closes the procedure checks todays date and if that date matches a preset archive date then a query runs to check for archive candidates. The main problem with this is that the database *has* to be open or closed on an archive date for it to run the procedure and make the match in dates - although to get around that problem I suppose you could work in some tolerance and make the procedure check 5 days each way of the archive date.

    Rather than trying to do this yourself (if you're not familiar with VBA) you could start by looking on the net for some other clever b'stard who's probably already done this themselves. Try www.download.com and look for freebies, and check the usual Access sites and Google of course.


    Regards - Andy

  10. #10
    Join Date
    Jan 2004
    Location
    Jersey, UK
    Posts
    108
    Here. I quickly wrote some code to get you started in case you want to tackle this yourself. This code will run an event if the date is 07/01 (07-Jan, in case you're a yank!!) so it will work on an annual basis. Dump it into a new module, save the module then create a macro that points to the code. If you call the macro autoexec, it will run everytime the db starts up.

    Public Sub CheckDate()
    Dim strToday As String
    Dim strArchiveDate As String

    'Set archive date
    strArchiveDate = "07/01"

    'Set todays date in right format
    'Note: if annual check, there's no need to include the year data
    strToday = CStr(Format(Now(), "dd/mm"))

    'Check for date match and run something accordingly
    If strArchiveDate = strToday Then
    'Run your archive query in this section
    MsgBox "Today is audit day!"
    DoCmd.RunSQL "qryCheckAudit"
    Else
    'Do nothing
    End If
    End Sub


    Regards - Andy

  11. #11
    Join Date
    Jul 2003
    Posts
    292
    Could I have it run to a function.. like this ?

    Function dltOldDate()
    Dim db As Database
    Dim ans As String

    Set db = CurrentDb()

    ans = msgbox("There are records that are five years old.. would you like to delete?", vbYesNo)

    If ans <> "6" Then
    msgbox "At this time the records have not been deleted!", vbOKOnly
    Exit Function
    Else
    db.Execute "Delete * FROM YourTable " _
    & "WHERE YourTable.YourRecord <= Date() - 1825"
    msgbox "Aged records have been deleted!", vbOKOnly
    End If

    End Function

    thanks

  12. #12
    Join Date
    Jul 2003
    Posts
    292
    ok what im going to do is.. just pick out the records that are 5 years old.. in a qry.. and have a button where they can press.. showing them the records.. there they can delete themselves..

    don't know why i didnt go this route..


    thanks for the help !

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •