Up For Renewal - Date Calculation

[Naja3005]

Hi all,

I'm new to this Forum and new also to Access. I have a contract database that I have to remind the contract owners that the contract is up for renewal in 90 days, 60 days and 30 days. I can't figure the correct formula for this calculation. The idea is to generate an email eventually to give them a headsup. My fields name are StartDate - EndDate. Thanks for your help.

 

[plog]

Use DateAdd to add/subtract days from a date:

MS Access: DateAdd Function

This MSAccess tutorial explains how to use the Access DateAdd function with syntax and examples. The Microsoft Access DateAdd function returns a date after which a certain time/date interval has been added.

www.techonthenet.com

 

[arnelgp]

you need more than just a Query for your situation.
probably need a Table to store the Contract Number, the period and
Today's Date.
the table will be used to Limit "once per 90, 60, 30 days"
the emailing.
this table is Indexed on Contact Number + period (90, 60, 30),
with No Duplicate.

example, if a certain contract will expires 90 days from now, it will
be added to the table and Todays date is added also.

ContractNo____________Period______________Date
11111-------------------90------------------19-Dec-2020

so you only email whose datefield is same as today's date.

next day, this contract will not be included since you cannot add it
again (dup ContactNo + Period).

after 30 days, this same ContractNo will be added (provided, it
did not Re-contract during the period).

ContractNo____________Period______________Date
11111-------------------60------------------18-Jan-2021

 

[theDBguy]

Hi. Welcome to AWF!

As you can see, you can use the DateAdd() function to calculate a new date from an existing one. So, if all your contracts are of the same term (annually, for example), then you may not even need to have an EndDate field.

Just a thought...