delete records

[Chi]

Hi,

My form and report that based on the table has many records. I would like
the old records deleted by themselves if their check out dates are pass the
current month.

Ex: check out date: 02/08/08. It will be deleting by itself if today is
03/01/08

The goal is instead of manually delete the old records that pass the current
month, I would like the database delete them for me. That will make the list
shorter and easier to keep track.

Thanks
Chi

 

[akphidelt]

This might not be what you want, but I'm definitely against deleting old
records. Once they're deleted you have no Archiving possibilities in the
future.

Easiest thing to do is just create a query with the data in it. And then
under the criteria in the date column put whatever the criteria is you want.
Like Date() - 3 or >#2/08/08#

Then your query will be constantly up to date and you can still access old
records in the tables.

 

[Evi]

Or at the very least, if your table grows very large very quickly, append
the poor records to an Archive table before deleting them from your present
table.
When you promise faithfully to do that, then we'll all start talking about
Append and Delete Queries.

The number of times I've deleted saying 'Well, I'll never need to refer back
to those again!".....

Evi

 

[Chi]

Thank you AKphidelt,

I really don't need the old records. However, thank you for your respond and
the useful criteria.
Chi

 

[John Spencer]

If you always want only records that are greater than the beginning of the
current month you can use the following criteria in a query to see only
those records

Field: SomeDateField
Criteria: >= DateSerial(Year(Date()), Month(Date()),1)

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Chi]

Thanks for the criteria. However, I would like to delete the old records out
off the database.

I am sorry if I am asking different question.


After the user entered the CHECK OUT DATE and close the form, that record
will be deleted.

Ex: In record 10, I enter 03/24/08 in the CHECK OUT FIELD and then close it.
I would like the record 10 will be deleted by itself so that when I reopen
the form, the record 10 will be gone.

Thanks
Chi

 

[Evi]

In the OnOpen Event of the form, just above the End Sub line, add this code


Dim MySql As String
My Sql = "DELETE YourTable.* FROM YourTable WHERE
(((YourTable.YourDateFeild)=Month([YourDateField])<Month(Date())))"
DoCmd.RunSql MySql



Replace YourTable and YourDateField with the real table and field names.
You'll be sorry!
Evi

 

[Evi]

If you've got any months lower than the current date from next year then
they will be deleted too so

Lets make that:

Dim MySql As String
My Sql = "DELETE YourTable.* FROM YourTable WHERE
(((YourTable.YourDateField)<DateSerial(Year(Date()),Month(Date())-1,Day(Date
()))));

(darned rosé)
Evi

In the OnOpen Event of the form, just above the End Sub line, add this code


Dim MySql As String
My Sql = "DELETE YourTable.* FROM YourTable WHERE
(((YourTable.YourDateField)=Month([YourDateField])<Month(Date())))"
DoCmd.RunSql MySql



Replace YourTable and YourDateField with the real table and field names.
You'll be sorry!
Evi

Thanks for the criteria. However, I would like to delete the old records out
off the database.

I am sorry if I am asking different question.


After the user entered the CHECK OUT DATE and close the form, that record
will be deleted.

Ex: In record 10, I enter 03/24/08 in the CHECK OUT FIELD and then close it.
I would like the record 10 will be deleted by itself so that when I reopen
the form, the record 10 will be gone.

Thanks
Chi

deleting

old

in

the

are

pass

make

the

 

[Chi]

Thanks Evi,

I entered the code, but I have "syntax error"

Here is the code:
Private Sub Form_Open(Cancel As Integer)
Dim MySql As String
MySql = "DELETE EQUIPMENT TABLE 2.* FROM EQUIPMENT TABLE 2 WHERE"
(((EQUIPMENT TABLE 2. CHECKEDOUT)=Month([CHECKEDOUT])<Month(Date())))"

DoCmd.RunSQL MySql

please help
Chi

In the OnOpen Event of the form, just above the End Sub line, add this code


Dim MySql As String
My Sql = "DELETE YourTable.* FROM YourTable WHERE
(((YourTable.YourDateFeild)=Month([YourDateField])<Month(Date())))"
DoCmd.RunSql MySql



Replace YourTable and YourDateField with the real table and field names.
You'll be sorry!
Evi

Thanks for the criteria. However, I would like to delete the old records out
off the database.

I am sorry if I am asking different question.


After the user entered the CHECK OUT DATE and close the form, that record
will be deleted.

Ex: In record 10, I enter 03/24/08 in the CHECK OUT FIELD and then close it.
I would like the record 10 will be deleted by itself so that when I reopen
the form, the record 10 will be gone.

Thanks
Chi

 

[Evi]

If you've got gaps in your table names then you'll need to put square
backets around it

[Equipment Table 2].*

See my next letter for a better code though. Don't want you deleting future
data. And please make a copy of your db and check the query on there,
before deleting anything using a delete query. Once you say OK to it,
everything will be gone, forever!!!


Evi

Thanks Evi,

I entered the code, but I have "syntax error"

Here is the code:
Private Sub Form_Open(Cancel As Integer)
Dim MySql As String
MySql = "DELETE EQUIPMENT TABLE 2.* FROM EQUIPMENT TABLE 2 WHERE"
(((EQUIPMENT TABLE 2. CHECKEDOUT)=Month([CHECKEDOUT])<Month(Date())))"

DoCmd.RunSQL MySql

please help
Chi

In the OnOpen Event of the form, just above the End Sub line, add this code


Dim MySql As String
My Sql = "DELETE YourTable.* FROM YourTable WHERE
(((YourTable.YourDateFeild)=Month([YourDateField])<Month(Date())))"
DoCmd.RunSql MySql



Replace YourTable and YourDateField with the real table and field names.
You'll be sorry!
Evi

Thanks for the criteria. However, I would like to delete the old

records
out

off the database.

I am sorry if I am asking different question.


After the user entered the CHECK OUT DATE and close the form, that record
will be deleted.

Ex: In record 10, I enter 03/24/08 in the CHECK OUT FIELD and then

close
it.

I would like the record 10 will be deleted by itself so that when I reopen
the form, the record 10 will be gone.

Thanks
Chi

:

If you always want only records that are greater than the beginning

of
the

current month you can use the following criteria in a query to see only
those records

Field: SomeDateField
Criteria: >= DateSerial(Year(Date()), Month(Date()),1)

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Thank you AKphidelt,

I really don't need the old records. However, thank you for your respond
and
the useful criteria.
Chi

:

This might not be what you want, but I'm definitely against

deleting
old

records. Once they're deleted you have no Archiving possibilities

in
the

future.

Easiest thing to do is just create a query with the data in it.

And
then

under the criteria in the date column put whatever the criteria

is
you

want.
Like Date() - 3 or >#2/08/08#

Then your query will be constantly up to date and you can still access
old
records in the tables.

:

Hi,

My form and report that based on the table has many records. I would
like
the old records deleted by themselves if their check out dates

are
pass

the
current month.

Ex: check out date: 02/08/08. It will be deleting by itself if today
is
03/01/08

The goal is instead of manually delete the old records that

pass
the

current
month, I would like the database delete them for me. That will

make
the

list
shorter and easier to keep track.

Thanks
Chi