Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Deleting Records based on a certain amount of time

Discussion in 'Information Technology' started by Jason, Nov 3, 2005.

  1. Jason

    Jason
    Expand Collapse
    Guest

    Is this possible, Say I have users that enter information and it is saved by
    radio day. is there a way to query the database table (that contains the
    information) and have it reference the current date and automatically delete
    the day if it is over 6 months old?
     
  2. Loading...


  3. Brendan Reynolds

    Brendan Reynolds
    Expand Collapse
    Guest

    DELETE *
    FROM tblTest
    WHERE (((DateDiff("m",[RadioDay],Date()))>=6));

    In query design view, it looks like this ...

    Field: DateDiff("m",[RadioDay],Date())
    Criteria: >=6

    --
    Brendan Reynolds

    "Jason" <Jason@discussions.microsoft.com> wrote in message
    news:D23C2A73-79C6-4395-9048-F65051D0DA10@microsoft.com...
    > Is this possible, Say I have users that enter information and it is saved
    > by
    > radio day. is there a way to query the database table (that contains the
    > information) and have it reference the current date and automatically
    > delete
    > the day if it is over 6 months old?
     
  4. Ofer

    Ofer
    Expand Collapse
    Guest

    First, please back up.

    You can use the DateAdd together with date() that return the current date,
    in the criteria of a delete query

    DELETE TableName.*
    FROM TableName
    WHERE TableName.DateField>DateAdd("m",6,Date())

    --
    If I answered your question, please mark it as an answer. That way, it will
    stay saved for a longer time, so other can benefit from it.

    Good luck



    "Jason" wrote:

    > Is this possible, Say I have users that enter information and it is saved by
    > radio day. is there a way to query the database table (that contains the
    > information) and have it reference the current date and automatically delete
    > the day if it is over 6 months old?
     
  5. Ofer

    Ofer
    Expand Collapse
    Guest

    Sorry, I ment

    DELETE TableName.*
    FROM TableName
    WHERE TableName.DateField>DateAdd("m",-6,Date())

    --
    If I answered your question, please mark it as an answer. That way, it will
    stay saved for a longer time, so other can benefit from it.

    Good luck



    "Ofer" wrote:

    > First, please back up.
    >
    > You can use the DateAdd together with date() that return the current date,
    > in the criteria of a delete query
    >
    > DELETE TableName.*
    > FROM TableName
    > WHERE TableName.DateField>DateAdd("m",6,Date())
    >
    > --
    > If I answered your question, please mark it as an answer. That way, it will
    > stay saved for a longer time, so other can benefit from it.
    >
    > Good luck
    >
    >
    >
    > "Jason" wrote:
    >
    > > Is this possible, Say I have users that enter information and it is saved by
    > > radio day. is there a way to query the database table (that contains the
    > > information) and have it reference the current date and automatically delete
    > > the day if it is over 6 months old?
     

Share This Page