Delete button

[wayney2507]

I have a table "Jobsheet" with a field "Date" within it.

I'd like to put a button on a form so that when clicked, it deletes all records with a date which is 90 days older than todays date.

Help please?

 

[pbaldy]

Try

CurrentDb.Execute "DELETE * FROM TableName WHERE FieldName < DateAdd('d', -90, Date())"

A field named "date" isn't a good idea, as it can conflict with the built-in Date() function.

 

[dfenton]

CurrentDb.Execute "DELETE * FROM TableName WHERE FieldName < DateAdd('d', -90, Date())"

Better:

CurrentDb.Execute "DELETE * FROM TableName WHERE FieldName < DateAdd('d', -90, Date())", dbFailOnError

And then you'd need to add an error handler.

Or you could use my SQLRun() function, which does all this for you:

http://groups.google.com/group/microsoft.public.access.formscoding/msg/c6b8298f1853ae4d

 

[wayney2507]

Ah i found out how do it the easy way. Make a 'delete query' using criteria "<Date()-90"

Thanks for the help though.

 

[dfenton]

Ah i found out how do it the easy way. Make a 'delete query' using criteria "<Date()-90"

This is not really any different from using DoCmd.RunSQL, as you have to call DoCmd.OpenQuery, and if you don't want to bother your users with the confirmation messages, you have to turn off SetWarnings and then turn it back on.

So, the exact same problems adduced for the other solutions are present the DoCmd.RunQuery, and the solution I posted is designed to avoid those problems.