How to delete records over 45 days old from current date

[TLCTech]

I am not sure where to put a command to delete these records.
Someone else told me to put this command in: DELETE FROM [tblRegistration] WHERE DateDiff("d", ArrivalDate, Date()) > 45

I have attached my tlbRegistration for your viewing.

[TLCTech]

Forgot to attach the file

tblRegistration.zip

[JoeM]

You can either run a Delete Query (see: https://www.fmsinc.com/microsoftacce...ete-query.html)
or you could use a Macro, picking the "RunSQL" Action and entering that code in the SQL Statement argument.

[TLCTech]

Still Having issues with deleting the records

[JoeM]

What kind of issues, exactly? Are you getting some sort of error message? If so, what?

Depending on where you are doing this, you may need to alter your statement.

If you are doing a Delete Query, I think you may need to alter your statement like this:

DELETE [tblRegistration].* FROM [tblRegistration] WHERE DateDiff("d", ArrivalDate, Date()) > 45

If you are trying the Macro option, the DateDiff has different syntax in SQL. It shouldn't be necessary though. In subtracting two dates, the default is days, so you should just be subtract the two dates. Also, SQL uses GetDate() instead of Date().

If you still need help please let us know which option you are pursuing, what your current code looks like, and what errors you are seeing.

[TLCTech]

Thank you for the quick response, I will try it.

[rpeare]

p.s. I would not delete your data at all, you never know when it might be useful or you may need to look at old records. Instead I would advocate you add a field to your table that was something like a field named 'ARCHIVE' that's yes/no and set the flag to yes (-1) when you run your process, then on your forms/reports you can set your criteria to look for only non archived records. In essence run an update query rather than a delete query.

[JoeM]

I would not delete your data at all, you never know when it might be useful or you may need to look at old records.

It is really hard to say for certain without knowing exactly what they are using it for.

Personally, I have databases that have temp tables that I clear out after every use, or I have other databases which just serve as "pass-through" applications for data format conversions. In either case, there is no need to keep the data in there, so a delete query seems in perfect order.

[rpeare]

Yes I do the same, I have tables I flush and refill every week with external data but if this is a registration database being able to see who registered for which event as time goes on, to me, would be extremely useful. OP would have to clarify, just throwing another option in there.

[JoeM]

Yes, I agree. If anything where historical information may be useful, it is best to keep it.