Delete Last Months Data From Table

[MJ]

Can someone help me with this, I am drawing a blank this morning?

BACKGROUND: My predecessor had a very simple (yet powerful "delete *")
query set to act after the archive process but for some silly reason it fails
to work when placed in the VB code, but works fine when executed manually.

PROBLEM: I have a table that collects data everyday and I need to purge the
data from the previous month out after that data has been copied to an
archive file. Each record contains a field called "Comment_Date" that
contains the date it was added to the table. I want to delete all records
with a "Comment_Date" in the previous month and leave any record from the
CURRENT month. This way I can get away from the all powerful "delete *" and
the potential risk of loosing current data if executed at the wrong time.

Thank you in advance for your time and assistance.

 

[Bob Barrows [MVP]]

Can someone help me with this, I am drawing a blank this morning?

BACKGROUND: My predecessor had a very simple (yet powerful "delete
*") query set to act after the archive process but for some silly
reason it fails to work when placed in the VB code, but works fine
when executed manually.

PROBLEM: I have a table that collects data everyday and I need to
purge the data from the previous month out after that data has been
copied to an archive file. Each record contains a field called
"Comment_Date" that contains the date it was added to the table. I
want to delete all records with a "Comment_Date" in the previous
month and leave any record from the CURRENT month. This way I can
get away from the all powerful "delete *" and the potential risk of
loosing current data if executed at the wrong time.

Thank you in advance for your time and assistance.

Show us what you tried and explain your symptoms rather than saying
"fails to work".
You should show us the complete sql statement as well.

 

[MJ]

Bob,

I included that my predecessor used... "DELETE *" and that their VB code
sequence was not working. It has been commented out since before I got here,
so I have no idea why or if it ever worked in PRODUCTION. But my real intent
was to start from scratch...

1. The archive piece is working correctly. [NO CHANGE NEEDED]

2. I am looking to build a query that NO MATTER WHEN (from the first of the
month to the last of the month), or HOW MANY TIMES, it is executed it can
only delete data from the PREVIOUS month.

A. I wish to stay away from BRUTE FORCE (delete *) because if it is not
timed correctly it can have disasterous results.

B. Each record has a date field (COMMENT_DATE) that can be used to filter
on.

C. The problem is to build a query that is smart enough, using the
current date, to create a date range (BETWEEN x AND y) where X is first of
the PREVIOUS month and Y is the end of the PREVIOUS month (without requiring
constant developer input).

Does someone out there have a simple solution to my dilema?

Thanks again for your insight...

 

[ANDY-N via AccessMonster.com]

You could use this criteria for the delete query in the date column:

= Month(Date()) <-- Deletes data of current month
<>month(date()) <-- Deletes data except durrent month
= Month(date()) -1 <-- Delete previous month data only

Bob,

I included that my predecessor used... "DELETE *" and that their VB code
sequence was not working. It has been commented out since before I got here,
so I have no idea why or if it ever worked in PRODUCTION. But my real intent
was to start from scratch...

1. The archive piece is working correctly. [NO CHANGE NEEDED]

2. I am looking to build a query that NO MATTER WHEN (from the first of the
month to the last of the month), or HOW MANY TIMES, it is executed it can
only delete data from the PREVIOUS month.

A. I wish to stay away from BRUTE FORCE (delete *) because if it is not
timed correctly it can have disasterous results.

B. Each record has a date field (COMMENT_DATE) that can be used to filter
on.

C. The problem is to build a query that is smart enough, using the
current date, to create a date range (BETWEEN x AND y) where X is first of
the PREVIOUS month and Y is the end of the PREVIOUS month (without requiring
constant developer input).

Does someone out there have a simple solution to my dilema?

Thanks again for your insight...

Show us what you tried and explain your symptoms rather than saying
"fails to work".
You should show us the complete sql statement as well.

 

[raskew via AccessMonster.com]

This working example selects all records from the month previous to the
current month:

SELECT tblDevProcess.*
FROM tblDevProcess
WHERE (((tblDevProcess.endTime) Between DateSerial(Year(Date()),Month(Date())-
1,1) And DateSerial(Year(Date()),Month(Date()),0)));

This working example selects all records from mm/yyyy input by the operator
when prompted. Probably a little safer than the first example.

SELECT tblDevProcess.*
FROM tblDevProcess
WHERE (((tblDevProcess.endTime)>=DateValue([enter mm/yyyy]) And
(tblDevProcess.endTime)<DateAdd("m",1,DateValue([enter mm/yyyy]))));

In either case, replace SELECT with DELETE and you've got a Delete query.

HTH - Bob

Bob,

I included that my predecessor used... "DELETE *" and that their VB code
sequence was not working. It has been commented out since before I got here,
so I have no idea why or if it ever worked in PRODUCTION. But my real intent
was to start from scratch...

1. The archive piece is working correctly. [NO CHANGE NEEDED]

2. I am looking to build a query that NO MATTER WHEN (from the first of the
month to the last of the month), or HOW MANY TIMES, it is executed it can
only delete data from the PREVIOUS month.

A. I wish to stay away from BRUTE FORCE (delete *) because if it is not
timed correctly it can have disasterous results.

B. Each record has a date field (COMMENT_DATE) that can be used to filter
on.

C. The problem is to build a query that is smart enough, using the
current date, to create a date range (BETWEEN x AND y) where X is first of
the PREVIOUS month and Y is the end of the PREVIOUS month (without requiring
constant developer input).

Does someone out there have a simple solution to my dilema?

Thanks again for your insight...

Show us what you tried and explain your symptoms rather than saying
"fails to work".
You should show us the complete sql statement as well.

 

[Bob Barrows [MVP]]

Bob,

I included that my predecessor used... "DELETE *" and that their VB
code sequence was not working. It has been commented out since
before I got here, so I have no idea why or if it ever worked in
PRODUCTION. But my real intent was to start from scratch...

I'm sorry, I thought you were asking us to debug some VB code. I did not
realize you needed a brand new query. Bob Askew's suggestion would be
the one I would go with.

 

[John W. Vinson/MVP]

You could use this criteria for the delete query in the date column:

= Month(Date()) <-- Deletes data of current month
<>month(date()) <-- Deletes data except durrent month
= Month(date()) -1 <-- Delete previous month data only

Actually, this would not work: Month() does not return a date, it
returns an integer 1 through 12 (Jan - Dec). if applied as a criterion
on a calculated field Month([datefield]) it would delete all records
for July, no matter what year that July fell in.

 

[MJ]

That is the MAGIC key, it works wonderfully!

Thank you, thank you, thank you

--

MJ

This working example selects all records from the month previous to the
current month:

SELECT tblDevProcess.*
FROM tblDevProcess
WHERE (((tblDevProcess.endTime) Between DateSerial(Year(Date()),Month(Date())-
1,1) And DateSerial(Year(Date()),Month(Date()),0)));

This working example selects all records from mm/yyyy input by the operator
when prompted. Probably a little safer than the first example.

SELECT tblDevProcess.*
FROM tblDevProcess
WHERE (((tblDevProcess.endTime)>=DateValue([enter mm/yyyy]) And
(tblDevProcess.endTime)<DateAdd("m",1,DateValue([enter mm/yyyy]))));

In either case, replace SELECT with DELETE and you've got a Delete query.

HTH - Bob

Bob,

I included that my predecessor used... "DELETE *" and that their VB code
sequence was not working. It has been commented out since before I got here,
so I have no idea why or if it ever worked in PRODUCTION. But my real intent
was to start from scratch...

1. The archive piece is working correctly. [NO CHANGE NEEDED]

2. I am looking to build a query that NO MATTER WHEN (from the first of the
month to the last of the month), or HOW MANY TIMES, it is executed it can
only delete data from the PREVIOUS month.

A. I wish to stay away from BRUTE FORCE (delete *) because if it is not
timed correctly it can have disasterous results.

B. Each record has a date field (COMMENT_DATE) that can be used to filter
on.

C. The problem is to build a query that is smart enough, using the
current date, to create a date range (BETWEEN x AND y) where X is first of
the PREVIOUS month and Y is the end of the PREVIOUS month (without requiring
constant developer input).

Does someone out there have a simple solution to my dilema?

Thanks again for your insight...

Show us what you tried and explain your symptoms rather than saying
"fails to work".
You should show us the complete sql statement as well.

 

[MJ]

Bob,

Not a problem. I know that quite a few people do ask for debug assistance,
but in this case I was more interested in getting away from earlier thinking
and move forward with safety in minf considering that this is working with
critical datasets.

Once again, thank you for your assistance.