How do I Delete records 120 days old?

  • Thread starter Thread starter Erik
  • Start date Start date
E

Erik

Created a database that imports records from Excel. Trying to create a query
that will delete records that are over 120 days old. Cannot figure out the
criteria to use in the delete query.
 
DELETE
FROM [YourTable]
WHERE [YourDateField] <= DateAdd("d",-120,Date())

In the query design view for a DELETE query, you would have

Field: YourDateField
Criteria: <= DateAdd("d",-120,Date())


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
 
John,
Thanks. I tried the first example however, that did not work. It would
delete records form the months within that 120 day range, but leave
everything else (records from January, for example). Will it make any
difference that the date column has the time as well? The format for the
column of data is:

dd/mm/yyyy hh:mm:ss

Thanks for your help.

Erik

John Spencer said:
DELETE
FROM [YourTable]
WHERE [YourDateField] <= DateAdd("d",-120,Date())

In the query design view for a DELETE query, you would have

Field: YourDateField
Criteria: <= DateAdd("d",-120,Date())


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
Created a database that imports records from Excel. Trying to create a query
that will delete records that are over 120 days old. Cannot figure out the
criteria to use in the delete query.
Click to expand...
Click to expand...
 
First question.
Is the Date column a DateTime field? Or is it a text field? If it is a text
field, you will need to force the date into a datetime type. See Allen
Browne's article on International Dates in Access at:
http://allenbrowne.com/ser-36.html

Second question. Do you want to delete records older than 120 days (based on
today's date) or do you want to delete records between 120 days ago and today?

What I posted should delete any records that have a date field on or before
8/18/2008 since today is 12/16/2008.

Having the time included in the date field will mean that any records that
have a time after midnight on the 18th would not be deleted. If you want the
18th change expression to

< DateAdd("d",-121,Date())



John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
John,
Thanks. I tried the first example however, that did not work. It would
delete records form the months within that 120 day range, but leave
everything else (records from January, for example). Will it make any
difference that the date column has the time as well? The format for the
column of data is:

dd/mm/yyyy hh:mm:ss

Thanks for your help.

Erik

John Spencer said:
DELETE
FROM [YourTable]
WHERE [YourDateField] <= DateAdd("d",-120,Date())

In the query design view for a DELETE query, you would have

Field: YourDateField
Criteria: <= DateAdd("d",-120,Date())


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
Created a database that imports records from Excel. Trying to create a query
that will delete records that are over 120 days old. Cannot figure out the
criteria to use in the delete query.
Click to expand...
Click to expand...
Click to expand...
 
FIrst Answer. The column in question is a Date/Time type. I will try this
and if it still does not work, I will post again. Thanks.

Erik


John Spencer said:
First question.
Is the Date column a DateTime field? Or is it a text field? If it is a text
field, you will need to force the date into a datetime type. See Allen
Browne's article on International Dates in Access at:
http://allenbrowne.com/ser-36.html

Second question. Do you want to delete records older than 120 days (based on
today's date) or do you want to delete records between 120 days ago and today?

What I posted should delete any records that have a date field on or before
8/18/2008 since today is 12/16/2008.

Having the time included in the date field will mean that any records that
have a time after midnight on the 18th would not be deleted. If you want the
18th change expression to

< DateAdd("d",-121,Date())



John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
John,
Thanks. I tried the first example however, that did not work. It would
delete records form the months within that 120 day range, but leave
everything else (records from January, for example). Will it make any
difference that the date column has the time as well? The format for the
column of data is:

dd/mm/yyyy hh:mm:ss

Thanks for your help.

Erik

John Spencer said:
DELETE
FROM [YourTable]
WHERE [YourDateField] <= DateAdd("d",-120,Date())

In the query design view for a DELETE query, you would have

Field: YourDateField
Criteria: <= DateAdd("d",-120,Date())


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Erik wrote:
Created a database that imports records from Excel. Trying to create a query
that will delete records that are over 120 days old. Cannot figure out the
criteria to use in the delete query.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top