Complicated Date calculation.

  • Thread starter Thread starter G deady via AccessMonster.com
  • Start date Start date
G

G deady via AccessMonster.com

I am hoping someone can help me. I have spent days on this problem and can't
figure it out. I have a db for my lawoffice. We need to track the Statutue
of Limitations for Auto Accident cases. It is two years from the accident
date unless the person is a minor . Then it is two years from the persons
18th birthdate. I need to write an IF Statement:
If AccidentDate - Dateofbirth <18 then StatuteLimits = DateofBirth = 20 years
ELSE Statlimits = AccidentDate plus two years.
Part of my problem is that my dateofbirth field is not bound to this form so
I am bringing the dateofbirth into an unbound textbox on this form through a
query for purposes of this calculation. I am so lost. Can someone pease
help me and tell me where to put it? Thank you.
 
You can get a more complete DateDiff Function from this location:
http://www.accessmvp.com/djsteele/Diff2Dates.html

You can use the Diff2Dates at the url above to calculate the number of years
between the Date of Birth and the Date of the Accident.

The Diff2Dates function as written will return the number of years like: "17
Years". However, you can have it return only the number of years if you will
locate the following code:

If booCalcYears And (lngDiffYears > 0 Or ShowZero) Then
varTemp = lngDiffYears '& IIf(lngDiffYears <> 1, " years", " year")
End If

and modify the following line of code:
varTemp = lngDiffYears '& IIf(lngDiffYears <> 1, " years", " year")
by placing the single quote just prior to the first ampersand sign.

You might want to use the AfterUpdate event of the Accident field to trigger
the following code which will call a function like the CalculateLimitDate
function shown further down:
'this code would cause the calculate date to be placed in a text box
formated for a ShortDate named "txtLimitExpire". You can substitute the name
of your control. This code passes the value from the Date of Birth field
(me.txtDOB or your control name) and the Date of the Accident
(me.txtAccidentDate or your control name):

If Not IsNull(Me.txtDOB) And Not IsNull(Me.txtAccidentDate) Then
Me.txtLimitExpire = CalculateLimitDate(Me.txtDOB, Me.txtAccidentDate)
End If

Create the CalculateLimitDate Function like:

Function CalculateLimitDate(DateOfBirth As Date, AccidentDate As Date) As Date
Dim bytYrsOldAtAccident As Variant
Dim curDateOfBirth As Date
curDateOfBirth = DateOfBirth
'calculate the age of the person at the time of the accident
'bytYrsOldAtAccident = DateDiff("yyyy", Me.txtDOB, Me.txtAccidentDate)
bytYrsOldAtAccident = Diff2Dates("Y", DateOfBirth, AccidentDate)

If bytYrsOldAtAccident < 18 Then
CalculateLimitDate = DateAdd("yyyy", 20, curDateOfBirth)
Else
CalculateLimitDate = DateAdd("yyyy", 2, AccidentDate)
End If
End Function

Just copy the "Diff2Dates" function from Doug Steels web site and paste it
in. Make the modification I mentioned. The function above will call the
Diff2Dates function and will return the calculated date.

Hope this helps.

Mr. B
 
G Deady,

Assuming you can base your form on a query that includes the
DateOfBirth, then you should be able to make a calculated field in the
query itself to give you the StatLimits date, or else in the Control
Source of an unbound textbox on the form. You are already most of the
way there. Something like this...
StatLimits:
IIf(DateAdd("yyyy",18,[DateOfBirth])>[AccidentDate],DateAdd("yyyy",20,[DateOfBirth]),DateAdd("yyyy",2,[AccidentDate]))
 
Steve, Thank You. I used your code with one modication. I found code to
take into account the difference between this a person's birthdate inthe
current year and accident date so as to insure complete accuracy. For
example. My daughter was born in 1985. If I just used her birthdate I would
get 20 years but her birthdate is not until August 28th so the actual
difference between her birthdate and an accident date would be 19 years.

I created a calculated textbox(Text27 and used the following calculation. It
calculates the number of years between the date of accident and the persons
date of birth then subtracts one if the month and day of accident is prior to
month and day of birthday. This gives me an accurate age.

=DateDiff("yyyy",[DOB],[AccidentDate])+Int(Format([AccidentDate],"mmdd")
<Format([DOB],"mmdd"))

Then in the afterdate event I used this code:

Private Sub AccidentDate_AfterUpdate()
If [Text27] < 18 Then
StatofLimits = DateAdd("yyyy", 20, [DOB])
Else: StatofLimits = DateAdd("yyyy", 2, [AccidentDate])
End If

End Sub

It works perfectfectly. Not exactly your code but you put me on the right
track. Thanks.
 
G Deadly,

I am happy to know that it is working for you now. Though I am puzzled
as to the extra complication you introduced... the expression I
suggested should be perfectly accurate for your purposes as is. Your
statement "if I just used her birthdate I would get 20 years" is not
correct.

The other thing that becomes apparent in your example is that apparently
StatofLimits is bound to a field in the table. This would generally be
regarded as not a good idea. This is "derived data" and should not be
stored in a table, it should be displayed as the result of a calculation
as required on your forms or reports. For example, if you have an
*unbound* textbox on your form, and you put this in the control source...

=IIf(DateAdd("yyyy",18,[DateOfBirth])>[AccidentDate],DateAdd("yyyy",20,[DateOfBirth]),DateAdd("yyyy",2,[AccidentDate]))
.... this is the way I would do it. And yes, it will take into account
that your daughter's birthday isn't until August!

Otherwise, to use the kind of code you have, I would personally forget
the Text27 idea, and do it like this...
Private Sub AccidentDate_AfterUpdate()
If DateAdd("yyyy",18,Me.DOB)>Me.AccidentDate Then
Me.StatofLimits = DateAdd("yyyy", 20, Me.DOB)
Else
Me.StatofLimits = DateAdd("yyyy", 2, Me.AccidentDate)
End If
End Sub
 
Now that I reread your code I understand what it does and I agree ut is much
better. As far as StatofLimits being bound to a table, in most circumstances
I would agree with you. But as I understand I the objection to storing a
calulation field in a table is accuracy -ie in most cases the the
calculation will change on a regular basis.I this instance, the Statute of
Limitation never changes. And it is the most critical date we must track.
I'd be afraid not to store it.
 
G Deadly,
... the Statute of
Limitation never changes. And it is the most critical date we must track.
I'd be afraid not to store it.
Click to expand...

At the risk of over-belabouring the point :-)
If you have the DOB of the person stored in a table, and if you have the
AccidentDate stored in a table, and if the StatOfLimits is the result of
a simple predictable calculation based on the DOB and the AccidentDate,
then in effect you do have the StatOfLimits stored already. It is
stored, nice and snug and secure, in the DOB and AccidentDate fields.
So there is no need to store it *again*, and there is no need to be
afraid :-)
 
Back
Top