Purging data from a table

[normie]

I have a table that we append each day that we use for a monthly report that is then sent to a file and saved outside of the database...Is it possible to purge the data from the table using a date range...would it be a macro or is something built in Access 2007? or would it just be a manual process which means other people would have to go in the table and could cause damge to the database

[orange]

See the Delete query samples here.

http://www.w3schools.com/sql/sql_delete.asp

A DELETE query can not be undone, so be very careful

[normie]

I found the article helpful but I am having a problem writing the SQL for a date range
Can someone sterr me in the right direction?
Thanks

[orange]

With a select query with a Datefield call MyDate where Datefield Between 21 Mar 2012 and 1 April 2012

SELECT * FROM MyTable
WHERE
DateField BETWEEN #3/21/2012# AND #4/01/2012#;

[normie]

I am using the "RUN SQL" action is this the right one? I tested "DELETE OBJECT" in a test DB and deleted the whole table

[orange]

You SHOULD NOT BE DOING DELETE anything until you understand the implications of such action.

Back up your table and/or your database.

DELETE is not "undoable". Things will be gone.

Get a Select query working first to make sure only the appropriate records are being selected. The sample given earlier would be a good model.

Then report back to tell us what you have done and what you have learned. And show us the query SQL that you are working with.

This is for your own protection.

[normie]

I got the reults I needed with a update query except that it only deleted the data and not the rows leaving blamks at the top of the table....did I miss a step?

[orange]

How did an UPDATE query get the results you need? You were going to DELETE records.

Seems you did not follow my advice re Select query.

Why not post your sql.

[normie]

I apoligize I did mean DELETE query (it was late when i wrote that) I used the date range of BETWEEN 03/01/2012 AND 03/07/2012 and got the results on the table except for the empty rows still showing...Those are what I want to get rid of as well

[orange]

As I said earlier, DELETE queries are NOT undoable.

You have to be explicitly clear as to what exactly identifies those records to be deleted.
Back up your table -- copy the whole table and save under a different name
Run a Select query with the exact criteria in the WHERE clause to make certain you have the proper records.
Then Run the Delete with the exact same WHERE clause.

DELETE queries DELeTE records Not fields within records.

At this point we have no idea of what fields are in your table, and what values are in those fields. Nor what you might have deleted.
Datefields are delimited by # in Access.

[normie]

I am using a TEST database for all this if I make a mistake I just refresh with a saved IMPORT so I am not worried about losing anything at this point
The query I ran worked fine I was able to use BETWEEN [Start Date] and [End Date] and used the pop up boxes to enter the date range it then deleted exactly what I wanted and entered
My issue is the blank rows that are at the top of the table...how do I delete these as well?
The fields in the table are visible in the attachment above

Thanks again for your guidance and especially your patience

[orange]

What makes a "blank row" a blank row in your table? I take it that you mean some fields are not populated. That's the point about
criteria that explicitly defines the records you want.

[normie]

In the attachment it shows my Table and the empty rows that were created when I deleted the data with the query....those are the rows I want to get rid of...For example if I delete all March data on April 15th I want the April data to move up to the first row below the column names

[orange]

Sorry, I can't make any sense of the pdf. It shows only the middle of a screen and I'm not able to see the whole thing.


Can you make and post an mdb version of your database?

[normie]

Unfortuanly I can't send the information but I am trying to explain that I have what I need i just want the whole row to go away when the data is deleted I do not want any space between the remaining data and the column names