Delete Records that are > 90 Days

[NFL]

I have a database that I would like to delete all records that are greater
than 90 days old in my table. The condition would be looking at the
"InitialContact" field in my table and the data type is set to "date" format.
I'm not sure how to approach this and I was thinking that I could create a
query to identify records that are over 90 days and then where do I go from
there?

Thank you,

 

[dymondjack]

I know I left a reply already but it isn't showing as of yet...


Make a SELECT query to pull the records you want... verify them

Change SELECT to DELETE and run the query to delete the records.

Use CurrentDB.Execute or DoCmd.RunSQL to run the query from code.

hth

--
Jack Leach
www.tristatemachine.com

- "Success is the ability to go from one failure to another with no loss of
enthusiasm." - Sir Winston Churchill

 

[JimBurke via AccessMonster.com]

Create a new query, select the table in question from the the Show Table
dialog that is displayed initially. Then, on the menu bar, select Query, then
Delete to make it a Delete query. Drag the * from the table display at the
top to the first query column. The 'Delete' row of the query display for that
column whould now show 'From'. Drag the IInitialContact field from the table
display to the 2nd query column and set the criteria for it to:

<= Date()-90

The Delete row of the query display for that column should now show 'Where'.

This should delete all rows in the table that have a date that is 90 days old
or older based on todays date. Be careful with your testing! When testing
deletes like this always make sure you have a backup copy of the DB
beforehand, just in case you have problems with the Delete.

If you wanted to do it in VBA rather than with a stored query you can just
create a sub or function with the following statement in it:

DOCmd.RunSQL "DELETE * FROM tableName WHERE InitialContact <= #" & Date()-
90 & "#"

where 'tableName' is the name of your table.

 

[NFL]

I like both of those options. I tried your VBA example and a dialog box
prompts for input with the name "InitialContact". How do I get rid of that?
InitialContact is the field I want to check and see if there are any records
older than 90 days.

Thank you!