bradford80,
Welcome to Bytes!
Is it possible to create a field which would say auto generate "Expired" if the expiration date has passed?
The short answer is "Yes."
The long answer is, "How do you want to do that?"
One thing about MS Access is that it won't do anything in the background unless you tell it to do something. So, things don't just "run" as if you had wound up a clock.
There are several approaches to your question.
One approach would be to check for the expiration of a record each time it is accessed--for example, when someone takes a look at that record, it checks to see if the date is expired and then updates an Expired flag that now indicates the record is expired. There may be some value in approaching it this way.
However, the most significant shortfall with that approach is that this will only update that flag when a record is expired
and it has been viewed. So, another approach might be for the database to assess all records when it is opened and check
all records for expiration and assign that flag if they meet the criteria. This allows you to run queries based upon that flag--for example, pulling a list of all expired records.
However, the one shortfall with that method is that it creates unnecessary information in the Database (it is a field based upon a calculation of information in the currect record--which is usually frowned upon). The Yes/No flag is a separate field that would take up additional space for each record. This is typically not an issue for the size of DBs most Access gurus develop--but the principle holds true.
So, my recommendation is to not use a flag, but simply use the criteria of the current date being greater than the Expiration Date. So, for example, every time you run any queries and need the expired records, your Criteria would be:
- WHERE Date() > [Expiration Date]
I hope this hepps you understand some of the underlying principles. But, it is up to you to determine the best way for your particular needs.
Standing by if you need additional assistance.
1
