Delete data more than 2 months old

[AJCB]

Hi there.

I have a field in a table which shows the date and time that a record was
created in Access.

I am wanting to run a query that will automatically delete data that is more
than 2 months old, but I cannot figure out what string to put in the query.

Can anyone help me?

Regards
AJ

 

[Allen Browne]

DELETE FROM [YourTableNameHere]
WHERE [YourDateTimeFieldHere] < DateAdd("m", -2, Date());

 

[Dennis]

Oops sorry, the criteria should be <Now()

 

[Dennis]

Create a query with your table as its source. Create a column in the query
like this
HowOld: DateAdd("m",2,[Date Created])
In the criteria fior this column put

Now()

Check the query return the correct results and then change its type to a
delete query

 

[AJCB]

Hi Dennis.

This did not work. I didn't get an error message, but all it put in the
field was the date and the time that the record was created.

Do you have any other ideas?

Create a query with your table as its source. Create a column in the query
like this
HowOld: DateAdd("m",2,[Date Created])
In the criteria fior this column put

Now()

Check the query return the correct results and then change its type to a
delete query

Hi there.

I have a field in a table which shows the date and time that a record was
created in Access.

I am wanting to run a query that will automatically delete data that is more
than 2 months old, but I cannot figure out what string to put in the query.

Can anyone help me?

Regards
AJ

 

[Dennis]

It works OK but obviously I cannot see what you have done. Have you tried it
Allen's way instead ?

Hi Dennis.

This did not work. I didn't get an error message, but all it put in the
field was the date and the time that the record was created.

Do you have any other ideas?

Create a query with your table as its source. Create a column in the query
like this
HowOld: DateAdd("m",2,[Date Created])
In the criteria fior this column put

Now()

Check the query return the correct results and then change its type to a
delete query

Hi there.

I have a field in a table which shows the date and time that a record was
created in Access.

I am wanting to run a query that will automatically delete data that is more
than 2 months old, but I cannot figure out what string to put in the query.

Can anyone help me?

Regards
AJ

 

[AJCB]

I will try, but I am not really that confident using SQL queries, as I
usually cannot get them to work.

It works OK but obviously I cannot see what you have done. Have you tried it
Allen's way instead ?

Hi Dennis.

This did not work. I didn't get an error message, but all it put in the
field was the date and the time that the record was created.

Do you have any other ideas?

Create a query with your table as its source. Create a column in the query
like this
HowOld: DateAdd("m",2,[Date Created])
In the criteria fior this column put
Now()
Check the query return the correct results and then change its type to a
delete query

:

Hi there.

I have a field in a table which shows the date and time that a record was
created in Access.

I am wanting to run a query that will automatically delete data that is more
than 2 months old, but I cannot figure out what string to put in the query.

Can anyone help me?

Regards
AJ

 

[John W. Vinson]

I am wanting to run a query that will automatically delete data that is more
than 2 months old,

Why?

You'll NEVER need to see historical data?

Bear in mind that deleting data will NOT free up space in your database (until
you Compact), and if you have suitable indexes, won't much affect the speed of
your queries, unless you have hundreds of thousands of records.

but I cannot figure out what string to put in the query.

< DateAdd("m", 2, Date())

will delete all records older than two months ago today. BACK UP YOUR DATABASE
first.