Results 1 to 8 of 8
  1. #1
    Join Date
    Dec 2002
    Location
    KY
    Posts
    56

    Red face Unanswered: Help me Please!!!!!!!!!!!!!!!!!

    Does anybody know how to Delete a record that is 3 years or older from a current date that is automatically entered???????????

  2. #2
    Join Date
    Nov 2002
    Location
    Hillsboro, OR, USA
    Posts
    59

    Re: Help me Please!!!!!!!!!!!!!!!!!

    try:

    WHERE RecordDate < DateAdd("yyyy",-3,#EnteredDate#)

    Brian

  3. #3
    Join Date
    Dec 2002
    Location
    KY
    Posts
    56

    Re: Help me Please!!!!!!!!!!!!!!!!!

    That suggestoin didn't work.
    I appreciate you guys help!!!!
    Please keep on sending me some suggestions because i'm tuckered 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 bri
    try:

    WHERE RecordDate < DateAdd("yyyy",-3,#EnteredDate#)

    Brian

  4. #4
    Join Date
    Nov 2002
    Location
    Hillsboro, OR, USA
    Posts
    59

    Re: Help me Please!!!!!!!!!!!!!!!!!

    cout? Are you using C++?

    The fine people on this board have been sending you perfectly good SQL snippets. Access uses VBA and can run SQL directly or through query objects.

    To be real explicit:

    In VBA do something like this:
    Code:
    DoCmd.RunSQL "DELETE tblYourTable.*, tblYourTable.Inspection_Date " & _
    "FROM tblYourTable ' & _
    "WHERE (((tblYourTable.Insepction_Date)=(Date()-1095)));"
    Or create a query named "qryDeleteThreeYearsBack" (or whatever)
    with the following SQL:
    Code:
    DELETE tblYourTable.*, tblYourTable.Inspection_Date
    FROM tblYourTable
    WHERE (((tblYourTable.Insepction_Date)=(Date()-1095)));
    Then just run it anytime you want to clean old records out or set up some VBA that looks like this:
    Code:
    Docmd.OpenQuery "qryDeleteThreeYearsBack"
    Brian

  5. #5
    Join Date
    Dec 2002
    Location
    KY
    Posts
    56

    Re: Help me Please!!!!!!!!!!!!!!!!!

    I'm doing the project in Access 97, but I was writing an example of what i'm trying to do in C, so people can have a clear understanding of what i'm trying to do.



    Originally posted by bri
    cout? Are you using C++?

    The fine people on this board have been sending you perfectly good SQL snippets. Access uses VBA and can run SQL directly or through query objects.

    To be real explicit:

    In VBA do something like this:
    Code:
    DoCmd.RunSQL "DELETE tblYourTable.*, tblYourTable.Inspection_Date " & _
    "FROM tblYourTable ' & _
    "WHERE (((tblYourTable.Insepction_Date)=(Date()-1095)));"
    Or create a query named "qryDeleteThreeYearsBack" (or whatever)
    with the following SQL:
    Code:
    DELETE tblYourTable.*, tblYourTable.Inspection_Date
    FROM tblYourTable
    WHERE (((tblYourTable.Insepction_Date)=(Date()-1095)));
    Then just run it anytime you want to clean old records out or set up some VBA that looks like this:
    Code:
    Docmd.OpenQuery "qryDeleteThreeYearsBack"
    Brian

  6. #6
    Join Date
    Dec 2002
    Location
    KY
    Posts
    56

    Re: Help me Please!!!!!!!!!!!!!!!!!

    On the second code you gave me where would you set the brackets for the SQL??????


    Originally posted by bri
    cout? Are you using C++?

    The fine people on this board have been sending you perfectly good SQL snippets. Access uses VBA and can run SQL directly or through query objects.

    To be real explicit:

    In VBA do something like this:
    Code:
    DoCmd.RunSQL "DELETE tblYourTable.*, tblYourTable.Inspection_Date " & _
    "FROM tblYourTable ' & _
    "WHERE (((tblYourTable.Insepction_Date)=(Date()-1095)));"
    Or create a query named "qryDeleteThreeYearsBack" (or whatever)
    with the following SQL:
    Code:
    DELETE tblYourTable.*, tblYourTable.Inspection_Date
    FROM tblYourTable
    WHERE (((tblYourTable.Insepction_Date)=(Date()-1095)));
    Then just run it anytime you want to clean old records out or set up some VBA that looks like this:
    Code:
    Docmd.OpenQuery "qryDeleteThreeYearsBack"
    Brian

  7. #7
    Join Date
    Nov 2002
    Location
    Hillsboro, OR, USA
    Posts
    59

    Re: Help me Please!!!!!!!!!!!!!!!!!

    This code should be correct:

    Code:
    DELETE YourTable.*, YourTable.Date
    FROM YourTable
    WHERE (((YourTable.Date)=(Date()-1095)));
    You need to place this code in a query object.
    - create a new query in design view
    - close the 'show tables' dialog without selecting any tables
    - select 'SQL View' from the 'View' menu
    - paste this code into the SQL window
    - replace 'YourTable' with the appropriate table name
    - save the query and run it (on test data of course!).

    Brian

  8. #8
    Join Date
    Dec 2002
    Location
    KY
    Posts
    56

    Re: Help me Please!!!!!!!!!!!!!!!!!

    What about adding a year_type of 1 that is also deleted with the inspection dates that are 3 yrs and older.

    Originally posted by bri
    This code should be correct:

    Code:
    DELETE YourTable.*, YourTable.Date
    FROM YourTable
    WHERE (((YourTable.Date)=(Date()-1095)));
    You need to place this code in a query object.
    - create a new query in design view
    - close the 'show tables' dialog without selecting any tables
    - select 'SQL View' from the 'View' menu
    - paste this code into the SQL window
    - replace 'YourTable' with the appropriate table name
    - save the query and run it (on test data of course!).

    Brian

Posting Permissions

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