Macro dates

[Anderson]

Hi

Forgive me if this seems like a bit of a rookie question - I'm trying to write a macro in access that will delete records that were created before a certain date (eg older than 6 months)

Any ideas how I might go about making one?

Thanks in advance

 

[Dennisk]

its a delete query you require, not a macro.

DELETE * FROM tblYourTable WHERE Yourdate <= #mm/dd/yyyy#

substitute the date values for mm/dd/yyyy (note the format)

But before you actually delete the records, create a select query first to determine if the correct record set has been selected. When satified, take a copy of the table (or database) then change the query to a delete and run.

 

[Anderson]

Brilliant thanks - I'll have a go at that

Is there any way of making it dynamic, so that it'll, say, remove entries from 6 months before today's date, and then 6 months from tomorrow's date if I run the same query tomorrow?

 

[boblarson]

DELETE * FROM tblYourTable WHERE Yourdate <= DateAdd("m", -6, Date())

 

[Anderson]

Genius
Thanks very much

 

[Anderson]

Forgive me for being a bit of a chump...

DELETE * FROM tblYourTable WHERE Yourdate <= DateAdd("m", -6, Date())

...Do I just copy the code and change the table name or do I need to change anything else?
Sorry, this is all a bit new to me. Cheers

 

[JANR]

You have to change "tlbYourtablename" and "YourDate" to what is in your db. Once you have done that copy the SQL and open a new Query with no tables, open the SQL-View and paste in the code. If you change to designview you can check that is look's ok and run the query. If you get an syntax error it's probably because of a missing semicolon at the end.

If you want to use macro to run the query you can use OpenQuery or RunSql methode. To use RunSql just copy the correct SQL and paste it in the sqlstring.

Hope this wasn't to confusing.

JR