Deleting data older than 6 months

[Digby]

Hi,

Firstly I rather unsucessfly tried the search function,

I want to automatically delete any enteries older than 6 months, I do NOT want to archieve them, simply delete them. I only have three entries;
Date
Address Line 1
Post Code

I was unsure how i would go about this or even where to begin so some help would be much appreciated.

Kind regards

 

[Dennisk]

Delete * from mytable WHERE DateAdd("m",6,myDate) < Date()

as date is a keyword do not use for a column name
what this does is to add 6 months to the date and test if it is still less than today's date

 

[Digby]

Hi,

sorry to sound stupid but where do i put the sql as i can not get it to work at all it comes up compile error and highlights "Delete" what am i doing wrong, as im not great with SQL i simply made a button in a form with wizzard then deleted the text that was put there and entered the sql given above, sorry for such a basic question cheers guys

 

[rainman89]

Probably easier than writing your sql
1.open query design in the queries window and select your table.
2.select the star from your table and the your date field.
3.right click and change query type to delete query
4. in your date column select delete where (DateAdd("m",6,[YourDateField]) < Date())
5. save query whatever u want too

Then in the on click of your button( on the form) enter
DoCmd.OpenQuery "YourQueryName"

That should do it

 

[Digby]

rainman, thank you

but....i did exactly as you said and it will not work, i save it then when i re open the query it has put the

(DateAdd("m",6,[YourDateField]) < Date())

in the next colum! what am i doing wrong???

 

[rainman89]

you have to change the yourdatefield to the name of your field. you called it date. but i would change it to something else since date is a reserved word

its normal that it is in a new column no worries on that

 

[Digby]

I changed it to EntryDate, but i will change it completly and try again! thanks

 

[luckyboat]

Don't use date as name of any text box, lable etc too. By the way, although query is simple. The code is easier to control