Calculating Age 1

[mybers]

Hello!

Just a simple question. Im not sure if this has been post previously but if it did...pls redirect me...

Q: I have a field: [birthdate] and an unbound label box:[age], Id like when I write a date (example: 3/15/1978, it will automatically compute the age on the label box, ex., 3/15/1978 = 26)

I hope this is clear...
Any help will be appreciated. Tnx

francis

 

[infinitx]

Hi Francis,

Search for a post called "Formula for Age" or go to this URL:

http://www.tek-tips.com/viewthread.cfm?SQID=800809&SPID=181&newpid=181&page=1

Hope this helps,
Alex

 

[mph1]

This should help you out.

http://www.mvps.org/access/datetime/date0001.htm

Good Luck!

An investment in knowledge always pays the best dividends.
by Benjamin Franklin

 

[scriverb]

Just copy and paste this code into a query or use in VBA to calculate the accurate age. Update the colored table and field names with yours. One more way of getting the answer:

[b]Age[/b]: IIf(Month([[red]yourtable[/red]]![[blue]BIRTH_DATE][/blue])=Month(Date()) And Day([[red]yourtable[/red]]![[blue]BIRTH_DATE][/blue]])=Day(Date()),CInt((DateDiff('d',[[red]yourtable[/red]]![[blue]BIRTH_DATE][/blue]],Date())/365.25)),CInt((DateDiff('d',[[red]yourtable[/red]]![[blue]BIRTH_DATE][/blue]],Date())/365.25)-0.5))

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[MichaelRed]

Whle oft discussed and regularly disputged, the various uses of fractional years does have the proclivity to produce errant values in some situations. There are numerous threads here within these fora (Tek-Tips). A search using [DOB | Age | Birth] and /o combinations thereof should produce a plethora of possabilities as well as some discussion of the merits (or lack thereof) of many soloutions.

In general, these soloutions either rely on the 'partial day / year' or the determiniation of w3heqather the 'current' date is prior to or later/equal to the date of birth to determine wheather to include the additioanl year in the simple datediff function.





MichaelRed
mlred@verizon.net

 

[BobJacksonNCI]

francis,

Obviously, age calculation is often needed and there are many ways to do it with Access. Unfortunately, many methods are not entirely accurate, as Michael pointed out. That is due to the storage design for dates, consisting of the number of days since January 1st, 0100, a decimal point, followed by the fraction of the current day. Errors can be made when calculating age mathematically because of the necessity to allow for leap years. I made my own share of errors when making nearly-correct suggestions, as you could find by searching the threads I participated in.

Although some may argue that, with care, the correct age on any day CAN be calculated without performing comparisions, I recommend you use the information contained in the link provided by mph1. By the way, I believe the slick shortcut that resembles:

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

was provided by Duane Hookum. (Credit where credit is due.)

I questioned how it works and was informed:

DateDiff calculates just the year difference and ignores everything else. This difference is then adjusted by the results of the comparison which produces a 0 (False) if
the birthday month and day have already passed and a -1 (True) if the birthday hasn't occurred yet.

If you want to try other solutions, please use a large enough sample so as to assure yourself of suitability to your situation.


HTH,
Bob

 

[scriverb]

francis: I agree with Michael and Bob concerning the number of times that AGE calculation threads have been started in these forums. Over the years I have labored with the problem of an accurage AGE calculations and have I believe developed a single expression that will always calculate the correct age. I have posted it before and have done so above. I have tested it against a very large database of dates running through all of the permutations and have not found any inaccurate calculations. I would be willing to email you a copy of the test database for your review if you send an email(see profile).

As for calculating the Age on your form the above expression can be changed to the following:

Me![Age]: IIf(Month(Me![BIRTHDATE])=Month(Date()) And Day(Me![BIRTHDATE]])=Day(Date()),CInt((DateDiff('d',Me![BIRTHDATE]],Date())/365.25)),CInt((DateDiff('d',Me![BIRTHDATE]],Date())/365.25)-0.5))

Give it a try and thoroughly test it out to become comfortable with its accuracy.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[scriverb]

Sorry, please change the colon) with an equals sign. Just a typo.

Me![Age] = . . .

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[bangsmic]

I am using Bobs DateDiff example above in an unbound text box in a report to obtain the # of years seniority. "hiredate" is the start date. I have been unsuccessful in breaking it down further to accurately show the number of months as well. The hang up seems to be any of my records where an employee has x number of years and 11 months.

I have read a lot of posts on the board about these issues. Seems awfully complicated for something seemingly so simple to figure in my head.

 

[MichaelRed]

hmmmmmmmmmmmmmmmm ... mmmmmmmmmmmmmmm

Mayhap your head isn't just following the "digital" brick road?

I would, again, refer to these fora, where the number of variations on the theme is larger than my patience, but I can assure you that this variation has been discussed and resolved.





MichaelRed
mlred@verizon.net

 

[TheAceMan1]

Howdy All . . . . .

Here's yet another [blue]Age Routine[/blue]. I've specifically designed it for DayCare Centers where Age is requred in years & months. Some of the centers I've done work for, have infants less than 1yr old. The routine returns a string like 12years 3months.

[blue]Public Function AgeCalc(BirthDay As Date) As String
   Dim qNow As Date, Years As Long, Months As Long
   Dim rtnYears As String, rtnMonths As String
   
   Const Yr As Integer = 12
   qNow = Int(Now())
   Months = DateDiff("m", BirthDay, qNow)
   
   If (Months Mod Yr = 0) And (Day(qNow) < Day(BirthDay)) Then Months = Months - 1
   [green]'If number of months is a multiple of 12, and the current day is less than the day
   'of the birthday, subtract 1 month. This allows the birthday to change when is arrives.
   'LeapYear is also covered in this way!
   
   'Format Years[/green]
   Years = Int(Months / Yr)
   If Years = 1 Then
      rtnYears = Years & "year"
   ElseIf Years > 1 Then
      rtnYears = Years & "years"
   End If
   
   [green]'Format Months[/green]
   Months = Months - (Years * Yr)
   If Months = 1 Then
      rtnMonths = Months & "month"
   ElseIf Months > 1 Then
      rtnMonths = Months & "months"
   End If
      
   [green]'Determine what to return[/green]
   If (rtnYears = "") And (rtnMonths = "") Then
      AgeCalc = "AgeErr!"
   ElseIf (rtnYears = "") And (rtnMonths <> "") Then
      AgeCalc = rtnMonths
   ElseIf (rtnYears <> "") And (rtnMonths = "") Then
      AgeCalc = rtnYears
   Else
      AgeCalc = rtnYears & " " & rtnMonths
   End If

End Function[/blue]

See Ya! . . . . . .