By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,827 Members | 1,967 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,827 IT Pros & Developers. It's quick & easy.

Create automatic flag based on expiration dates?

P: 1
I put together a quick database for agreements, which have an executed date and an expiration date. Is it possible to create a field which would say auto generate "Expired" if the expiration date has passed?
Jan 9 '19 #1
Share this Question
Share on Google+
2 Replies

Expert Mod 2.5K+
P: 3,158

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:

Expand|Select|Wrap|Line Numbers
  1. 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.
Jan 9 '19 #2

Expert Mod 15k+
P: 31,299
Yes it's possible.

However, it's frankly a daft idea. Such an indicator should be created as part of a query that you report on and should not be used to update data as you'd be creating dependent data. That would be non-normalised and generally a BAD(tm) idea. See Database Normalisation and Table Structures for more on that concept.
Jan 10 '19 #3

Post your reply

Sign in to post your reply or Sign up for a free account.