Date deletes data

  • Thread starter Thread starter fapa via AccessMonster.com
  • Start date 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?
 
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
 
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]
 
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...
 
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
 
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
:)
 
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
 
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
 
Back
Top