Page 1 of 2 12 LastLast
Results 1 to 15 of 30
  1. #1
    Join Date
    Dec 2002
    Location
    KY
    Posts
    56

    Red face Unanswered: I need help with a query on deleting a record

    How do you delete a record the is 3 years or older using a query?
    PLEASE HELP!!!!!!!!!!!!!!!!!

  2. #2
    Join Date
    Nov 2002
    Location
    Ohio
    Posts
    90
    Your query should look like this:

    DELETE FROM {TableName} where {DateField} <= #12/02/1999#

    Substitute your tablename for {TableName} and the data field being checked for {DateField}.

    The pound signs around the date may be optional, Access needs them, other databases do not.

    Good Luck,
    Bruce Baasch

  3. #3
    Join Date
    Dec 2002
    Location
    KY
    Posts
    56
    OK, i understand that part, but how do i delete from a current date that is automatically entered? Using a Delete Query?


    Originally posted by Bruce A. Baasch
    Your query should look like this:

    DELETE FROM {TableName} where {DateField} <= #12/02/1999#

    Substitute your tablename for {TableName} and the data field being checked for {DateField}.

    The pound signs around the date may be optional, Access needs them, other databases do not.

    Good Luck,

  4. #4
    Join Date
    Nov 2002
    Location
    Ohio
    Posts
    90
    I'd use DateAdd("YYYY", -3, Now) in VB to get a date three years ago, but I can't find the equivalent anywhere in the Access Help files.

    Sorry I don't have a better idea.
    Bruce Baasch

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    datediff should do it

    datediff("y",yourdate,date())

    rudy

  6. #6
    Join Date
    Aug 2002
    Location
    Northampton, England
    Posts
    266
    What do you mean by automatically entered ?

    David

  7. #7
    Join Date
    Dec 2002
    Location
    KY
    Posts
    56
    None the suggestion's worked!!!!!!!!!!
    I appreciate you guys help!!!!
    Please keep on sending me some suggestions because i'm tucked out on it.
    Let me send you an if statement of what I want the Delete query to do:

    Date() = means current Date, as in today's date!!!!!!!!

    if (Inspection_Date is 3yrs < Date())
    cout << Delete from Archive Table;
    else
    cout << Keep in Archive Table:



    Originally posted by DJN
    What do you mean by automatically entered ?

    David

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    delete from yourtable
    where datediff("y",yourdate,date()) >= 3

    could you please confirm, this did not work?


    rudy

  9. #9
    Join Date
    Dec 2002
    Location
    KY
    Posts
    56
    it didn't work, it says that i'm getting a data type mismatch, but I checked the data type fields for my archived table and Boiler table and the Date Entered and Inspection_Date for both tables are set to Date/Time.




    Originally posted by r937
    delete from yourtable
    where datediff("y",yourdate,date()) >= 3

    could you please confirm, this did not work?


    rudy

  10. #10
    Join Date
    Nov 2002
    Location
    Maaseik, Belgium
    Posts
    12
    It is not so difficult, just create a query wich has the following layout in SQL;

    DELETE {YOUR TABLE}.{TEST DATE}
    FROM {YOUR TABLE}
    WHERE (((DateDiff("d",{TEST DATE},Now()))/365>=3));

    or

    DELETE {YOUR TABLE}.{TEST DATE}, (DateDiff("d",{TEST DATE},Now()))/365 AS Differance
    FROM {YOUR TABLE}
    WHERE ((((DateDiff("d",{TEST DATE},Now()))/365)>=3));


    you can also use this where; datediff("yyyy",[date],now()>=3, but this gives problems because it first sepperates the year from the date and then looks if the difference is 3 or more, it doesnt look at the days or months. for instance: today = 02/01/2002; date = 20/12/1999
    normally the 3 years are passed on 21/12/2002, but access substracts only the years (2002-1999 = 3) and also deletes this.

    but i have tested the first, and it works....

    please correct me if i write anny mistakes, i'm also just a human and still a "freshman" in the world of programming, and i can only learn from it...

    kind regards.

    D
    Last edited by dgeu6397; 12-03-02 at 05:10.

  11. #11
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    if # is the pound sign what sort of pound is it because it definatly ain't GBP beacuse thats so unless it's an aberviation of the weight pound thats been lost over here since we went metric i don't have a clue what it is, we call it the hash symbol,

    always wondered about that and nows as good a time to ask as any other

    Originally posted by Bruce A. Baasch
    ...
    The pound signs around the date may be optional, Access needs them, other databases do not.

    Good Luck,
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  12. #12
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    this was something i used to select the last 31 days

    TRANSFORM Sum(qryMachineOutputByDate.SumOfGoodCount) AS SumOfSumOfGoodCount
    SELECT qryMachineOutputByDate.PostingDate
    FROM qryMachineOutputByDate
    WHERE (((qryMachineOutputByDate.PostingDate) Between Date()-31 And Date()))
    GROUP BY qryMachineOutputByDate.PostingDate
    PIVOT qryMachineOutputByDate.MachineID;

    so shouldn't this work
    DELETE {YOUR TABLE}.{TEST DATE}
    FROM {YOUR TABLE}
    WHERE {test date} < (date()-(365*3))

    after all a date is just a count of the days since 01/01/1900 or something like that
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  13. #13
    Join Date
    Nov 2002
    Location
    The Netherlands
    Posts
    61

    Question

    Isnt a # -> sharp or something. Like in C# = C sharp

  14. #14
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    thats the musical uses of the sign yes
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  15. #15
    Join Date
    Nov 2002
    Location
    Maaseik, Belgium
    Posts
    12
    Originally posted by DeathWing
    Isnt a # -> sharp or something. Like in C# = C sharp
    the correct term is a bracket i think??? no???

Posting Permissions

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