Date deletes data

  • Thread starter fapa via AccessMonster.com
  • Start date
F

fapa via AccessMonster.com

Hi

Would anyone know what the coding would be if i wanted to delete an entry on
a certain date?

E.g. i have data that i only want to store for 8 months. After 8 months, i
want it to be automatically deleted, Can this be possible?
 
A

Arvin Meyer [MVP]

Delete * From MyTable Where MyDateField > DateAdd("m", 8, Date())

Change the names to use your own. The above query deletes everything from
the table older than 8 months. Run the above query every day the database
opens from the opening form.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access
 
J

John Vinson

Delete * From MyTable Where MyDateField > DateAdd("m", 8, Date())

Change the names to use your own. The above query deletes everything from
the table older than 8 months. Run the above query every day the database
opens from the opening form.
Click to expand...

Arvin, shouldn't that be < and -8? As written it will delete all
records where MyDateField is more than eight months in the future...

Here. Have a cup of fresh-brewed Ethiopian Harrar coffee... <g>

John W. Vinson[MVP]
 
T

tina

PMFJI, Arvin, but am i reading that backward? it looks like it says
"Delete all records where the date field is in the future of (today's date+8
months)."

would the following work instead?
Delete * From MyTable Where MyDateField < DateAdd("m", -8, Date())
or "Delete all records where the date field is prior to (today's date-8
months)."

hth


Arvin Meyer said:
Delete * From MyTable Where MyDateField > DateAdd("m", 8, Date())

Change the names to use your own. The above query deletes everything from
the table older than 8 months. Run the above query every day the database
opens from the opening form.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

fapa via AccessMonster.com said:
Hi

Would anyone know what the coding would be if i wanted to delete an
Click to expand...
entry
Click to expand...
 
T

TC

Are you sure that you want to delete it?

If you do that, you won't be able to run statistics etc.
retrospectively.

The more common way, would be to filter your forms and reports for the
date range that you want at any particular time, for some particular
purpose. For example, you might filter on last week's data, or last
months, or the last 8 months, or the last 5 years worth, or all the
data ever entered for J.Smith, and so on.

HTH,
TC (MVP Access)
http://tc2.atspace.com
 
F

fapa via AccessMonster.com

Thank you all soo much for your help - it works perfectly! i just have one
question relating to 'running the query everyday the database opens from the
opening form' - would u be able to tell me how this can be done? thanks again
:)
 
T

tina

you could use an AutoExec macro to run the query. the macro will execute
each time the database is opened. or you could use a macro or VBA code to
run the query in the Open event of the "opening form".

hth
 
A

Arvin Meyer [MVP]

fapa via AccessMonster.com said:
Thank you all soo much for your help - it works perfectly! i just have one
question relating to 'running the query everyday the database opens from the
opening form' - would u be able to tell me how this can be done? thanks
Click to expand...
again

The simplest way I know:

1. Save the query with the name qryDeleteOldRecords
2. In Access Startup properties (Tools ... Startup) name the opening form.
3. In the form's On Open event, select: [Event Procedure] and type in the
event:

Private Sub Form_Open(Cancel As Integer)
On Error Resume Next

DoCmd.SetWarnings False
DoCmd.OpenQuery "qryDeleteOldRecords"
DoCmd.SetWarnings False

End Sub
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access
 
Top