Form and Age calculation (1 Viewer)

[klynch0803]

I'm trying to show someones age in a form using a text box.

I have a form and it has a textbox "Birthdate" and a textbox called "CurrentDate" and I want to use another textbox "Age" to display the age. If the form is searched and displays another date it would change the age as the "Birthdate" changes.

I have this code I researched and found but it doesnt seem to work at all in my instance. I dont get any errors it just shows a blank text box. Any ideas?

----Code Start------

Private Sub Age_beforeupdate(Cancel As Integer)
Dim TotalMonths, Years, Months As Integer

TotalMonths = DateDiff("m", Birthdate, CurrentDate)
Years = TotalMonths \ 12
Months = TotalMonths Mod 12
Age = Years & " Years and " & Months & " Months"
End Sub

----End Code----

 

[John Big Booty]

Why not just set the following code as the Control source for your text box?

=(Now()-[dob])/365.25

 

[klynch0803]

That would be to simple LOL..

Only problem with that is if I put decimal places at "0" it rounds up how can I tell it to not round up if over or equal to .5?

 

[John Big Booty]

Try this;

=Int((Now()-[dob])/365.25)

 

[klynch0803]

TY that works perfectly and much simpler...

 

[klynch0803]

Heres another one for you that maybe you can simplify...

What if I had a "ApplicationDate" text Field and a "RenewedDate" and I wanted it to compare those and see if one of them is greater than June 1st of the previous year.

For example today is 02/07/2008 and either the "ApplicationDate" or the "renewedDate" need to be greater than 06/01/2007. If one of them are then it would show "Current Membership" in the Text box if neither of them are true then it would show "Membership Needs Renewed!" in the text box.

 

[Luddite Lad]

You could do an IF OR logical test of the two dates against Date() and then show the appropriate result.

 

[dsfcom]

Try this;

=Int((Now()-[dob])/365.25)

I was using a formula similar to yours John but it doesn't calculate the age correctly for all given dates I've found. The formula below actually seems to work "accurately" for all given dates and provides an accurate age.

DateDiff("yyyy",[Birthday],Now())+Int(Format(Now(),"mmdd")<Format([Birthday],"mmdd"))

See post http://www.access-programmers.co.uk/forums/showthread.php?t=157188&highlight=calculate+age for more info.

 

[John Big Booty]

Interesting, I've just done a check for today (October 5) and my formula overestimates the age by a day on 54 occasions for birth dates of October 6 dating back to 1900.

 

[dsfcom]

You've proved my point John. I was having age miscalculations because of that single day deviation and came across another method that seems to work a little more accurately for now.

 

[RexesOperator]

I seem to recall somewhere that Microsoft forgot that 1900 wasn't a leap year. This meant that all calculations after 1900 are out by one day (unless they've fixed that):

http://en.wikipedia.org/wiki/List_of_leap_years

 

[John Big Booty]

The problem lies in my initial use of Now() rather than Date()

=Int((Date()-[dob])/365.25)

will return the same result, far more simply, as

DateDiff("yyyy",[Birthday],Now())+Int(Format(Now(),"mmdd")<Format([Birthday],"mmdd"))

The problem with the Now() function in this situation is that includes a time component as well as the date and this throws out the calculation. Date() on the other hand returns the date only.

 

[dsfcom]

Thanks for the update and learning session!