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

Calculating Anniversary Date

P: 2
Admin:This thread was originally posted as a hijack in Calculating next x anniversary dates.
Hello Stewart.

I am trying to calculate a date in the past relative to a future date that has been determined by a calculated field in a query. The past date needs to be calculated exactly 1 (or 12 months) year prior to the calculated future date, taking into account leap years on either side of the equation.

I apologize in advance if the following information is "too much" detail, but it seemed best to give the background I am dealing with that may be useful to help me with the problem I am trying to solve. Many thanks in advance.

Here is a some background:

I have been working with Access databases for about 10 years now. I would consider myself a low-level, intermediate user, at best, when it comes to database administration. I am one of the people responsible for maintaining the database.

I am working with an Access 2010 database that has data and objects that were originally created in Access 2000. The original database has been "converted" recently to Access 2010, but much of the data and the applicable field settings, especially in the primary table used, have been retained and not altered. Most of the core objects in the database have also been retained as unaltered. Most things work well, with some modifications to the newer Access 2010 coding as needed.

In the primary table used in this database, dates are stored to track license term dates. The license term is standardly 1 year long, but the initial, first license term can be less than 1 year long. The primary fields used for tracking the license term dates are date fields as follows:

Effective Date: This is the date the license term begins

Expire Date: This is the date the license term ends

Examples:

Full, 1 year (12 month) long license term

Effective Date: 1/1/2017
Expire Date: 12/31/2017

Partial, 6 month long license term

Effective Date: 6/1/2017
Expire Date: 12/31/2017

We prepare renewal data for the next future licensing period via a query with numerous date fields. The primary date field used to calculate the future dates, is the Expire Date field.

Our calculated future dates were becoming incorrect and I suspected, and confirmed that the calculations were yielding incorrect future dates due to leap years not being accounted for in the calculations. The incorrect dates were always in a future leap year.

Here is how we have the future date calculation set up currently:

We call the future Effective Date the Begin Date and the future Expire Date the End Date. These fields are named with alias's and the calculated data is not stored. It is only used to populate a spreadsheet from the exported query datasheet.

Calculated fields currently in use:

Begin Date: [Expire Date]-365
End Date: [Expire Date]+365

These fields will calculate correctly for a common year, but not a leap year, so we have been changing the subtraction and addition intervals to things like: +366, -366, or even things like +365-365+1 to get the correct future dates. These changes need to be done manually and at the correct period before a leap year will be in affect during the next licensing period. This is not preferable, nor do I believe the smartest way to use Access. I believe that correctly calculated dates can be achieved every time with a properly set up calculated field.

After numerous hours of internet research, I finally found your example here of how you had solved a similar problem with an IIf statement in a calculated query field.

Your solution to getting an future anniversary date, factoring in leap years:

DateSerial(Year(Date()) + IIf(Month([DOB])*100+Day([DOB])<=Month(Date())*100+Day(Date()),1,0), Month([DOB]),Day([DOB])) AS Anniversary

I modified your example using my field name, Expire Date, as follows:

End Date: DateSerial(Year([Expire Date])+IIf(Month([Expire Date])*100+Day([Expire Date])<=Month([Expire Date])*100+Day([Expire Date]),1,0),Month([Expire Date]), Day([Expire Date]))

This expression yields the correct future End Date every time for both common and leap years.

Now I need to subtract exactly 1 year (or 12 months depending on how this needs to be considered) from the calculated End Date to get the correct Begin Date.

I have tried numerous calculations using variations of the above example, and DateAdd function with various intervals ("yyyy", "d", "m", "q") to subtract from the calculated End Date and can only get correct calculations if the Begin Date falls in a leap year. If the Begin Date falls in a common year, the calculation is off by 1 day, 1 day too short of a full year. Nothing else has worked and some variations have yielded incorrect future dates from the original calculated End Date.

Any assistance would be greatly appreciated.

Thanks,

Jason
Feb 23 '19 #1

✓ answered by twinnyfo

Jason,

Your question may best belong in a new thread. However, we can quickly provide an answer for this, since it is somewhat related.

In your case, I would recommend using the DateAdd() function, adding a number of Years or Months to the begin date, and then subtracting 1.

Annual Example:
Expand|Select|Wrap|Line Numbers
  1. ExpireDate = DateAdd("yyyy", 1, BeginDate) -1
Six-Month Example:
Expand|Select|Wrap|Line Numbers
  1. ExpireDate = DateAdd("m", 6, BEginDate) - 1
Hope this hepps!

Share this Question
Share on Google+
4 Replies


twinnyfo
Expert Mod 2.5K+
P: 3,284
Jason,

Your question may best belong in a new thread. However, we can quickly provide an answer for this, since it is somewhat related.

In your case, I would recommend using the DateAdd() function, adding a number of Years or Months to the begin date, and then subtracting 1.

Annual Example:
Expand|Select|Wrap|Line Numbers
  1. ExpireDate = DateAdd("yyyy", 1, BeginDate) -1
Six-Month Example:
Expand|Select|Wrap|Line Numbers
  1. ExpireDate = DateAdd("m", 6, BEginDate) - 1
Hope this hepps!
Feb 24 '19 #2

P: 2
twinnyfo,

Thanks for your quick response and suggestions. I have not yet attempted to use the DateAdd function in the way you proposed. I will give it a shot and reply back.

I did figure out a work around using some variations to my existing calculated fields, but your suggestions may be simpler and more elegant.

I am finding that working with date calculations that need to factor in (or out as it may be) leap years can be tough, even for Access pros.

I wasn't sure if my question would be sufficient for a new thread considering I started this process with the information from a posting by Stewart Ross on this thread. I am new to posting on these forums, please forgive my rookie mistake.

Thanks,

Jason
Feb 24 '19 #3

twinnyfo
Expert Mod 2.5K+
P: 3,284
Using DateAdd() avoids all issues with Leap Years. There are times when date serial can be useful. However, DateAdd() can be easier in most cases.
Feb 24 '19 #4

NeoPa
Expert Mod 15k+
P: 31,494
Beware of posting code samples from a word processor. They typically mangle the quotes into characters that work for the printed word, but do not work in code. This will cause users to have non-working code should they try to copy your samples and post them into their own code.
Apr 13 '19 #5

Post your reply

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