Dates in delete queries

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello, can anyone help me... i haven't been able to figure this out yet.

I have a table that has fair amount of information in, and i don't want the
database to get too big. I also has to keep the information for a certain
amount of time.

I have a field named [Date Requested] and i want to be able to delete any
record in that table that is older that 3 or 4 years old. The format of the
dates in the [Date Requested] field is 'dd-Mmm-yy'. i don't know the correct
syntax (i guess) to get it to work correctly. How can i go about doing that?

Did i give enough information/ make any sense? if not, let me know.

Thank you in advance for helping me
 
CMEODSmith,

Try this first in a backup copy of your database! Make a new query
based on your table. Put the * from the table in the first column of
the query design grid, and the Date Requested field in the second
column. Make it into a Delete Query (select Delete from the Query
menu). In the Criteria of the Date Requested column, put this...
<DateAdd("yyyy",-3,Date())
Running this query will remove records from the table that are more than
3 years old. It will be possible to automate this process by the use of
macro or VBA procedure so that the old records are periodically purged.
 
Thank you greatly Steve Schapel. it worked. i don't know what i did wrong the
first time i tried that but i must of had a glitch. thanks a million!
Jay

Steve Schapel said:
CMEODSmith,

Try this first in a backup copy of your database! Make a new query
based on your table. Put the * from the table in the first column of
the query design grid, and the Date Requested field in the second
column. Make it into a Delete Query (select Delete from the Query
menu). In the Criteria of the Date Requested column, put this...
<DateAdd("yyyy",-3,Date())
Running this query will remove records from the table that are more than
3 years old. It will be possible to automate this process by the use of
macro or VBA procedure so that the old records are periodically purged.

--
Steve Schapel, Microsoft Access MVP

Hello, can anyone help me... i haven't been able to figure this out yet.

I have a table that has fair amount of information in, and i don't want the
database to get too big. I also has to keep the information for a certain
amount of time.

I have a field named [Date Requested] and i want to be able to delete any
record in that table that is older that 3 or 4 years old. The format of the
dates in the [Date Requested] field is 'dd-Mmm-yy'. i don't know the correct
syntax (i guess) to get it to work correctly. How can i go about doing that?

Did i give enough information/ make any sense? if not, let me know.

Thank you in advance for helping me
Click to expand...
Click to expand...
 
Back
Top