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
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
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.
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. ;-)
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.
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.