Results 1 to 5 of 5
  1. #1
    carstenhdk is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    May 2010
    Location
    Denmark
    Posts
    42

    Question Automatically delete posts

    Hi wise guys!




    I need some kind of function that automatically deletes post. I have a table with names and their personal information. After 3 month their registration they will be automatically deleted. How could this be done?




    Carsten

  2. #2
    TheShabz is offline Court Jester
    Windows XP Access 2003
    Join Date
    Feb 2010
    Posts
    1,368
    I'll answer this in two parts. First I'll tell you how to accomplish what you want. Second I'll tell you why you shouldn't do it/

    Make a delete query that deletes the records that have a registration date (or whatever you used for your date) of 90 days prior to today (the day you run it). You can manually run this query whenever you want or set it as a scheduled task.

    DELETE *
    FROM myTable
    WHERE registrationDATE = date()-90

    Now, here's why you shouldn't delete them. There are many times where you may still want that information (archived data). Personally, what I would do is create another field in that table called StatusInd (Ind = indicator) which would hold one value if it is an active account, and one if it is not. So instead of a DELETE query, you would run an UPDATE query. Now whenever you want to pull information on your active records, just insert a WHERE statusInd = -1 (assuming -1 = active, 0 = inactive).

    If this answers your question. Please mark the thread solved.

  3. #3
    carstenhdk is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    May 2010
    Location
    Denmark
    Posts
    42
    Thx for the help. I believe this solves the problem. Iīm rather new to database work, so I need to know where to put this code. :-) Then I will mark this thread as solved. ;-)

  4. #4
    maximus's Avatar
    maximus is offline Expert
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Aug 2009
    Location
    India
    Posts
    931
    Thats a million dollar quetion where will you put the code?

    Well I am not an expet but I think the the problem could be solved by creating a query with a calculative Field.

    for example:
    Create a Query with a Calculative Field Status with Code:

    Status: IIf(DateDiff("d",[Date],Date())>=90,"Canceled","OK")

    This Calculates the Difference of the date in the Field Date and Todays date in Days.

    or

    Status: IIf(DateDiff("m",[Date],Date())>=3,"Canceled","OK")

    This Calculates the Difference of the date in the Field Date and Todays date in Months.

    In the First Case the Status will Show cancelled when Days is more than or equal to 90 days.

    In the First Case the Status will Show cancelled when Days is more than or equal 3 months.

    I like doing my calculations in queries and not in tables. This will certainly save you from creating a new table and running an update query at regular intervals.

  5. #5
    carstenhdk is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    May 2010
    Location
    Denmark
    Posts
    42
    Quote Originally Posted by maximus View Post

    Status: IIf(DateDiff("d",[Date],Date())>=90,"Canceled","OK")

    Status: IIf(DateDiff("m",[Date],Date())>=3,"Canceled","OK")
    The first [date] I have replaced with my date feild.


    I canīt seem to get this working properly. It returns an error message: The entered expression contains a not illegal syntax.
    You have perhaps entered an operand without an operator.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. I want to automatically pass a value to a query
    By Slategrey252 in forum Queries
    Replies: 1
    Last Post: 10-01-2009, 05:38 AM
  2. Automatically get Excel data
    By TimG in forum Import/Export Data
    Replies: 2
    Last Post: 08-24-2009, 06:38 AM
  3. linking forms by ID - automatically add ID
    By jlcaviglia-harris in forum Forms
    Replies: 0
    Last Post: 04-17-2009, 03:51 PM
  4. Have database open automatically
    By Norman Masson in forum Access
    Replies: 6
    Last Post: 01-08-2008, 07:41 AM
  5. automatically create queries
    By GEORGIA in forum Programming
    Replies: 8
    Last Post: 01-23-2006, 02:35 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums