Expiry date calculation using Access 2013

Question

I am creating a database that keeps track of test dates and expiry.  The expiration can vary between 3-5 years depending on extension being granted.  Is there a way I can automatically have the expiry date changed as extensions are granted based on the original test date?

Answer 1

Guys, can you help me to my access data,

Because in my table field already manually input the date of expiration

and I want automatic beside on that field to put remark "expired" text.

I don't know how can I do that.

This is my table, I want like this when I type in the DATE FIELD its automatic will generate the status remark like "expired, valid or NA (Not available)... Help me guys thanks.

expiration	Status
1/2/2016	Expired
3/3/2018	Valid
	NA
1/29/2016	Expired

Answer 2

SELECT TestID, DateAdd("yyyy",3+[Extensions],[Testdate]) As ExpiryDate, etc. 

FROM tblTest

WHERE ExpiryDate BETWEEN #1/1/18# AND #6/1/18#;

the etc would be any other fields you want in the query results.

Answer 3

Do you have a field in your table that stores the length of time before expiration? If so, is that value stored in years? 

If not, then how do you know how to calculate the expiration date?

To answer your question it is possible to calculate the expiration date (and it should be a calculation), but we can't suggest an expression to use without more info. We can only suggest looking at the DateAdd function.

Answer 4

I'm afraid that won't work. Because it doesn't allow for no extensions. So I would add an Integer field to your table named Extensions. 

I would then use either an option group or combobox to select from 0, 1 or 2. I would then calculate the Expiry date using an expression like:

DateAdd("yyyy",3+[Extensions],[TestDate])

to display the Expiration date.

Answer 5

Hi Scott, thank you for taking the time to get back to me.

I do not have a field that stores the length of time before expiry.

I have a field that stores the original test date (which requires a 3 year expiry).  I would like to have a field that i can simply put a yes or no which reflects whether 1 or 2 extensions (resulting in an additional 1 or 2 years on the expiry) have been granted.  I hope this helps.