Complicated Date Calculation

[Gilrucht]

I have a db for my lawoffice and am trying to track the Statutute of Limitations for Auto Accidents. The calculation is complicated by the fact that the 2 year Statute of Limitation doesn't begin for a minor until he or she turns 18 so I can't create a simple calculation adding to years to my accident date field. I;m thinking I need a IF statement:
IF AccidentField - BirthdateField <18
BirthdateField plus 20 years
Else AccidentDateField plus 2 years.
Am I right and can someone tell me the correct syntext for this code? One problem is my birthdate field is in a different table. What is the best way to deal with this?
Which event would be the best one to put it in?

 

[Xanadu]

I have a db for my lawoffice and am trying to track the Statutute of Limitations for Auto Accidents. The calculation is complicated by the fact that the 2 year Statute of Limitation doesn't begin for a minor until he or she turns 18 so I can't create a simple calculation adding to years to my accident date field. I;m thinking I need a IF statement:
IF AccidentField - BirthdateField <18
BirthdateField plus 20 years
Else AccidentDateField plus 2 years.
Am I right and can someone tell me the correct syntext for this code? One problem is my birthdate field is in a different table. What is the best way to deal with this?
Which event would be the best one to put it in?

Why not use the DateAdd function to determine how many days the driver is away from their 18th birthday? If the DateAdd number is positive, add it to the AccidentDateField. If the number is negative, the driver is over 18 and you just add 2 years (or 728 days).

 

[Mile-O]

Begin with an Age function (there's one or two in the Code Repository) to get the person's age.

Let's say the function is called CalcAge, we put it into the following expression

=IIf(CalcAge([BirthDate], [AccidentDate]) < 18, DateAdd("yyyy",20,[BirthDate]),DateAdd("yyyy",2,[AccidentDate]))

 

[Gilrucht]

Miles,
I searched the repository as you suggested and found this sample that you had posted:


Public Function CALCULATEAGE(ByVal dteDOB As Date, Optional SpecDate As Variant) As Integer

On Error GoTo Err_CalculateAge

Dim dteBase As Date
Dim intCurrent As Date
Dim intEstAge As Integer

' Determine if SpecDate parameter has been passed
If IsMissing(SpecDate) Then
' If not, use current date
dteBase = Date
Else
' Otherwise use the SpecDate parameter
dteBase = SpecDate
End If

' Create an estimated age by getting the number of years
' between the date of birth and the secondary date
intEstAge = DateDiff("yyyy", dteDOB, dteBase)
' Create the exact age from evaluating the month and day based
' on the year
intCurrent = DateSerial(Year(dteBase), Month(dteDOB), Day(dteDOB))
CALCULATEAGE = intEstAge + (dteBase < intCurrent)

Exit Function

Err_CalculateAge:
CALCULATEAGE = -1

End Function

I created a module with this code but I still have the problem of my birthdate field being in another table. I need a way to pull that data into this form for this calculation. Any suggestions?

 

[Mile-O]

I would have thought you could bring them together in a query. Surely, the clients table's Primary Key is a foreign key in the Accident Table or they are linked via a junction table since clients to accidents is a many-to-man relationship.

 

[Gilrucht]

Sorry, I wasn't thinking. You are right. The autoaccident form is based on a query. I simply added mu birthdate field to the query and posted it to a textbox on the form. However I am getting the #Name? error message.
My previous post lists the code I took from the Public Function I took from the code repository and used to create a module. It was posted by you. I didn't change a thing. Then I used the calculation you gave me modified to my fieldnames:

" =IIf(CALCULATEAGE([Texr22],[AccidentDate])<18,DateAdd("yyyy",20, [Text22]),DateAdd("yyyy",2,[AccidentDate]))"

Text22 is the name of the textbox that holds the birthdate from the combobox when the query is run.
I created a textvox and placed this xalculation in the control source. I got the #name? error. I got the same error when I tried it in the default value property of the Statute of Limitations field which is where it ulrimately must go. What am I doing wrong?

 

[Mile-O]

You have a typo in the textbox name. Texr22

 

[Gilrucht]

Miles,
After I got overmy embarassment I corrected the typo but that didnt fix the problem. Now I get a #error. I get this error message whether I put the code in the control source of a textbox or in the default value of the statute of Limitations. Here is the code with the typo corrected:

=IIf(CALCULATEAGE([Text22],[AccidentDate])<18,DateAdd("yyyy",20,[Text22]),DateAdd("yyyy",2,[AccidentDate]))

Could the problem be in the module I created? It just occurred to me that the fields in the funcrion have to match my fields but I'm not sure which field goes where. Do I change "dteDOB" to the name of my dob field or to to "text22" since I am pulling the date into a textbox for this calculation? Would the "specdate" be changed to "accidentdate"? What are dtebase,intCurrent and intEstAge? Here is the function:

"Public Function CALCULATEAGE(ByVal dteDOB As Date, Optional SpecDate As Variant) As Integer

On Error GoTo Err_CalculateAge

Dim dteBase As Date
Dim intCurrent As Date
Dim intEstAge As Integer

' Determine if SpecDate parameter has been passed
If IsMissing(SpecDate) Then
' If not, use current date
dteBase = Date
Else
' Otherwise use the SpecDate parameter
dteBase = SpecDate
End If

' Create an estimated age by getting the number of years
' between the date of birth and the secondary date
intEstAge = DateDiff("yyyy", dteDOB, dteBase)
' Create the exact age from evaluating the month and day based
' on the year
intCurrent = DateSerial(Year(dteBase), Month(dteDOB), Day(dteDOB))
CALCULATEAGE = intEstAge + (dteBase < intCurrent)

Exit Function

Err_CalculateAge:
CALCULATEAGE = -1

End Function"

I really appreciate your help. I know I am being a pain.

 

[boblarson]

Just a thought - but

Text22 is the name of the textbox that holds the birthdate from the combobox when the query is run.
I created a textvox and placed this xalculation in the control source. I got the #name? error.

You need to put the FIELD name into the formula and NOT the name of the textbox, if this is a bound form. Also, if you have the text box with the same name as the field (not applicable in this case I would think), you will also get the #Name error.

 

[Gilrucht]

Bob, the field is in another table. That is why I am using a textbox. My query pulls the data from the field in the other table and posts it to the textbox in this form.

 

[Gilrucht]

Miles,
Just wanted to thank you and let you know I solved this problem. I gave up on the module but found another calculation in the code repository that calculated a persons exact age. I sent it in a calculated textbox: It takes 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 of the accident field I modified the code you gave me above as follows:

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.