Dates in delete queries

C

CMEODSmith

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
 
S

Steve Schapel

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.
 
C

CMEODSmith

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...
 
Top