How to calculate annual renewal dates 1

[jimger]

I'm creating a database to track licenses given out by our agency. We will be entering the current license holders in the database, as well as new applicants. One of the reports that I've been asked to create as a part of this db is a listing of license holders due for an annual review. The review process is supposed to start 120 days before the anniversary date of license approval. I've got a table that contains license information, and a field in that table for the approval date. I'd like to be able to calculate the annual review due date from this approval date. For example, if the approval date is 11/20/1994, the next review due date would be 8/22/2005 (120 days before 11/20/2005). I've looked at the DateAdd function, but since the approval dates are strung out over the past 10 years or so, I don't think that will work. Ideally, the calculation would also work for new approvals - calculating a review due date in 2006 for a license that was approved this year.

 

[jtseltmann]

Did I miss something or is 11/20/2005 less 120 days 7/23(or 7/22 depending)?

This rough function calculates out to your review date

Public Function FigureDate(thisdate As Date) as date

Dim datNewdate As Date
Dim varYY As Variant
Dim varMM As Variant
Dim varDD As Variant

varDD = Day(thisdate)
varMM = Month(thisdate)
varYY = Year(thisdate)

datNewdate = varMM & "/" & varDD & "/" & (varYY + 1)
FigureDate = datNewdate - 120

End Function

 

[jimger]

Yep, you're right about the date - I counted wrong (which is why I need the code!). The above function works fine for new approvals - calculates the date one year ahead, less 120 days. I was hoping, though, to find something that would give me the 2005 renewal date for my 1994 licence approvals (and my 2001 approvals, etc). Also, I was hoping to run this query for a few years, getting the new review date each year. I guess what I need is a way of converting the approval date into the same month and day,but for the current year. Then I could reference this date in the above function to get the date I want.

 

[jtseltmann]

You can expand this function or modify it to be called from a sub that would loop through and update your table. You said you have a table with a 'approval date' in it. Do you have a field for renewal date? If so then you can create a simple subroutine to pass through and udpate this 'renewal date' field using the above function and pass it each records 'approval date'.

I guess I am not getting a good feel for how you intend on maintaining this or how you really need it to work. I wanted to get you started with a sample of how to do the calc.

Can you give me some more info?

 

[jimger]

jseltmann -
I think I've come up with a solution, based on your code. What I want my query to return is the date that the review should start each year; in my 11/20/1994 example, I want the query to spit out 7/22/2005. If I run the query again next year, I want to see 7/22/2006. This is what I came up with - I'm fairly new at this, so I'm pretty sure there's another way, but this one seems to work.

Public Function FigureDate(AprDat As Date) As Date

Dim datNewdate As Date
Dim varYY As Variant
Dim varMM As Variant
Dim varDD As Variant
Dim Renyr As Integer
Dim ThisDate As Date

varDD = Day([AprDat])
varMM = Month([AprDat])
varYY = Year([AprDat])
ThisDate = Date
If Month(AprDat) < Month(ThisDate) Then
Renyr = (DateDiff("yyyy", [AprDat], ThisDate)) + 1
Else
Renyr = DateDiff("yyyy", [AprDat], ThisDate)
End If

datNewdate = varMM & "/" & varDD & "/" & (varYY + Renyr)
FigureDate = datNewdate - 120

End Function

Thanks for your help - I'd have never come up with a solution without it.
Ginger

 

[jtseltmann]

Great, glad to help you along.
j