delete records for last month

[eugz]

Hi All.
I need to modify my database that give me ability to delete all record form table for last month. How to solve that problem?
Thanks.

 

[KenHigg]

As always proceed with caution (and a backup .mdb). I would simply do a delete query that prompts for a date range...


ken

 

[coyote]

Check this link

http://www.databasedev.co.uk/automate_ms_access_archive.html

See if it will work for you.

 

[rainman89]

As always proceed with caution (and a backup .mdb). I would simply do a delete query that prompts for a date range...


ken

be very careful when doing these like ken said...

you may end up losing records you didn't mean too. also used improperly you can lose stability in your db...

do you really need to delete them?

 

[eugz]

I not realy familiar how to delete record automaticaly by schedule. Can you explain me detail?
Thanks.

 

[KenHigg]

...

do you really need to delete them?

IMHO - Excellent point. From a business perspective, I've rarely seen a situation where deleting records was the best option. Somewhere down the road the historical data may be useful. I would recomend archiving the data...


ken

 

[Kinger43]

If you have dates in your database you should be able to make a query with SQL

DELETE tablename.* FROM tablename WHERE tablename.date < DateAdd(m,-1,Date()).

DateAdd will subtract one month from the current date. The SQL statement above will delete all records older than one month prior to the current date.

 

[rainman89]

IMHO - Excellent point. From a business perspective, I've rarely seen a situation where deleting records was the best option. Somewhere down the road the historical data may be useful. I would recomend archiving the data...


ken

Totally agree.

 

[raskew]

Hi-

Try:
between dateserial(year(date()), month(date())-1, 1) and dateserial(year(date()), month(date()), 0)

Kinger - your dateadd() solution brings up only one date, e.g. today is 16-Oct-07, the dateadd() solution returns only 16-Sep-07.

Best Wishes - Bob

 

[Kinger43]

This is true. But he wants to delete everything older than that date, hence the date <