Delete data more than 2 months old

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" wrote:

> 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

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?

"Dennis" wrote:

> 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" wrote:
>
> > 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

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

"AJCB" wrote:

> 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?
>
> "Dennis" wrote:
>
> > 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" wrote:
> >
> > > 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

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

"Dennis" wrote:

> It works OK but obviously I cannot see what you have done. Have you tried it
> Allen's way instead ?
>
> "AJCB" wrote:
>
> > 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?
> >
> > "Dennis" wrote:
> >
> > > 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" wrote:
> > >
> > > > 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

On Fri, 4 Jul 2008 03:09:01 -0700, AJCB <AJCB DeleteThis @discussions.microsoft.com>
wrote:

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

John W. Vinson [MVP]

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

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"AJCB" <AJCB.DeleteThis@discussions.microsoft.com> wrote in message
news:7B61CF07-A879-4EF6-8AD2-1EFD317214E2@microsoft.com...
> 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