Deleting year record in Access

[dhen21]

Hi all,

Need help to delete records of previous years via VBA code where input from user is needed (to input the year).
I have my Date table that has (mm-dd-yyyy hh:mm:ss) format.



Thanks

[ranman256]

you don't need vba, make a query:
delete * from table where year([field]) = [Enter Year to Delete]"

[Micron]

This practice presents some danger if not accounted for, such as deleting from years that should not be, simply over a keyboard typo. Archiving is a method that you might want to consider as deleting records in a repeated fashion isn't usually done. At least that's my perspective.

[rawlstonb]

.... IRS Audit?

[dhen21]

Hi

Thanks for your feedback, we are archiving each year in the Excel File. The purpose is to get rid of the archived(per year) data.
Btw, just also interested in archiving method, can you advise me on how to do.

Thanks

[Micron]

Most common (I think) is an ArchivedDate field or some such name. I use camel case names so not Archived_Date as I'm too lazy to type underscores, and NEVER a space in between. But I digress.

To archive the record, your form/query writes Date or Now (both are built in functions) to a record. Subsequent queries can therefore show records that are current (... WHERE ArchivedDate Is Null) or have been archived (...WHERE ArchivedDate Is Not Null) or > than some date or between two dates, if you prefer. You could use a checkbox field, but a date field is more informative in that you can know when it was archived. You also cannot search a checkbox field with operators such as <, >, <>, BETWEEN, etc. dates.

That is all on the understanding that this involves Access tables, as I'm not sure how your Excel reference comes into play.