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 year (or 12 months) 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 to the professional that may be able 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 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 an example of how someone had solved a similar problem with an IIf statement in a calculated query field.
Found at this website https://bytes.com/topic/access/answe...iversary-dates
Credit to Stewart Ross
His 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 this 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.
Many thanks in advance.
jsallen17