Date Difference into field

[kingnothingz]

Hi all,

I have a situation wherein i have to calculate the age of a person at a particular time based on two dates.

Eg:
Date Of Birth = 01/01/1990
Date of Insurance Start = 14/02/2000
Age at Insurance start = ??
--------
I'm using DateDiff as follows

DateDiff("YYYY",DateOfBirth,DateOfInsurance)

This is fine, but my problem is that the dates are entered in a form and i need to display and store the output of the DateDiff in another field (AgeAtInsurance)

Not sure how to go about doing this.

Any help in this regard is appreciated.

Thanks

 

[missinglinq]

OK, Giz, exactly how is the statement

AgeAtInsurance = ("YYYY",DateOfBirth,DateOfInsurance)

going to calculate anything? Perhaps you meant to add DateDiff in there?

At any rate

DateDiff("YYYY",DateOfBirth,DateOfInsurance) can't be used to calculate age, because it only calculates the difference in years between the year component of the two dates.

Using this expression, with

DOB = June 1, 1989

DateOfInsurance = January 1, 2009

the result you'd get for "age" would be 20, when, in fact, since 0n 1/1/2009 the person hadn't had a birthday for the year 2009 yet, their age would actually be 19! They wouldn't be 20 years of age until 1/6/2009.

So you need an expression that accounts for this:

Private Sub DOBField_AfterUpdate()
 Me.AgeAtInsurance = DateDiff("yyyy", [DOBField], DateOfInsurance) - IIf(Format$(DateOfInsurance, "mmdd") < Format$([DOBField], "mmdd"), 1, 0)
End Sub

Uncle's other point is well taken; you need to structure your code so as to insure that both DOB and DateOfInsurance have data in them before doing your calculation. The AgeAtInsurance should not really be stored in the table, but merely re-calculated anytime you need it.

A copy of the code should be in the form's Form_Current event (so that when you move from record to record it will be correctly adjusted for the current record) and a calculated field used in reports whenever needed.

 

[missinglinq]

And they say that the legs are the first thing to go!

But most anything can be forgiven of anyone who's ever had to navigate the Robin Hood Roundabout!

 

[kingnothingz]

The DateOfInsurance should not really be stored in the table, but merely re-calculated anytime you need it.

You mean the AgeAtInsurance here right?

 

[kingnothingz]

Thanks Uncle and Linq,

Both the Date fields (DateofBirth & DateofInsurance) are mandatory fields in the table, so i don't have to worry about that.

But when I try the code by linq, i still dont get any results for AgeAtInsurance. What i've got now is like this:

Private Sub DateofBirth_AfterUpdate()

Me.AgeAtDiag = DateDiff("yyyy", DateofBirth, DateOfInsurance) - IIf(Format$(DateOfInsurance, "mmdd") < Format$(DateofBirth, "mmdd"), 1, 0)

End Sub

Private Sub Form_Current()

Me.AgeAtDiag = DateDiff("yyyy", DateofBirth, DateOfInsurance) - IIf(Format$(DateOfInsurance, "mmdd") < Format$(DateofBirth, "mmdd"), 1, 0)

End Sub

When I look at the Form Design, the AgeAtIsurance field is "Unbounded" doesnt this mean this is not being stored in the table?

I know in an ideal world, we wouldn't want to store this in the table, but the requirement is this way.

 

[missinglinq]

Well, if your field is named

AgeAtInsurance

and the code you're using says

Me.[B]AgeAtDiag[/B] = DateDiff("yyyy", DateofBirth, DateOfInsurance) - IIf(Format$(DateOfInsurance, "mmdd") < Format$(DateofBirth, "mmdd"), 1, 0)

it's not going to work, is it? AgeAtInsurance and AgeAtDiag aren't the same thing, are they?

 

[kingnothingz]

Sorry, It was meant to be AgeAtInsurance. Still doesn't work tough.

 

[missinglinq]

Are all of your field names spelled correctly? Does the DateOfInsurance field have a date in it when you enter the DateOfBirth?

I just copied the code you posted and pasted it into a form and it works as it should.

 

[kingnothingz]

Yep,

I had a few typos in there. The code works as it should. Thanks guys.

The trick (as uncle mentioned) is to default the dates to Date(). I did this in the table and let the user choose the right date in the form.

 

[missinglinq]

The devil's in the details! Glad you got it working!