Calculate age based on specific date

[esu4edp]

I have a Date of Birth field (DOB) and and Age Field (Age) and a Report Date Field (Report Date). I want the age field to automatically calculate the age of the person based on the day the report was taken on. How do I do this. Thanks!

 

[Bagger]

You might like to try this code. I can not take credit for it. Have used it in several applications I developed.
Option Compare Database
Option Explicit

Function AgeInYears(BirthDate As Date, Optional AsOfDate As Date = 0) As Double
'------------------------------------
'Purpose: Get Age in years with a given birthdate
'Returns: Age in Years
'Arguments: BirthDate
' AsOfDate - if specified will calculate age on that date
' if not specified Today's date will be used
'Uses: IsLeapYear()
'Notes: None
'Revised: 2/21/2000
'------------------------------------
Dim intAgeYear As Integer
Dim intAgeDays As Integer
Dim dblAge As Double
Dim intDivisor As Integer
If AsOfDate = 0 Then AsOfDate = VBA.date
intAgeYear = DateDiff("yyyy", BirthDate, AsOfDate)
intAgeDays = DateDiff("d", DateSerial(Year(AsOfDate), Month(BirthDate), Day(BirthDate)), AsOfDate)
'set divisor for determing part of year
intDivisor = 365
If IsLeapYear(Year(AsOfDate)) = True Then intDivisor = 366
If intAgeDays <> 0 Then
AgeInYears = intAgeYear + (intAgeDays / intDivisor)
Else
AgeInYears = intAgeYear
End If
End Function

Function IsLeapYear(YearNumber As Integer) As Boolean
IsLeapYear = (Day(DateSerial(YearNumber, 2, 28) + 1) = 29)
End Function

 

[CosmoKramer]

In your age text box, set up something like this as its control source:

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

 

[zinkey]

hi..
i have a similar calculation...how can i add a date and months... for example i have commencement date feild and a duration field.. i want to add these two and get end date... my problem here is i can't get the duration field input as months... for example if i want it to be 3months i have write 90 in that field.. how can i write 3 instead of 90 for three months and expect data base to take it as three months..
thank you

 

[WildHare]

Change the DURATION guy to a number field, not a date field.

Date + Number = DATE
e.g. &quot;04/24/02&quot; + 90 : 07/24/02

You'll need to use the &quot;M&quot; option in the DateAdd function:

EndDate = DateAdd(&quot;m&quot;, 3, CommencementDate)

Jim How many of you believe in telekinesis? Raise my hand...
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare2.htm

 

[zinkey]

thanks a lot man...i really appreciate ur help..and one more thing is there a format in access that will accept data as months.. (To set a field as mths )


egs :

Duration (Mth) : ''Values'' ---> i wan access to interpret this values as mths instead of days. Egs : Value = 1 & access will take value as 1 mth instead of 1 day.

thanks again

 

[MichaelRed]

Public Function basDOB2Age(Dob As Date, Optional AsOf As Date = -1) As Integer

    'Dob is just the date of Birth
    'AsOf is an optional date to check - as in examples 1 & 2
    'Otherwise, the DOB is checked against the Current Date

    'Michael Red 12/15/2001
    'To Calculate Age from Date of Birth
    
    'Sample Useage:
    '? basDOB2Age(#8/21/1942#, #8/21/2022#)
    '80

    '? basDOB2Age(#8/21/1942#, #8/20/2022#)
    '79

    '? basDOB2Age(#8/21/1942#)
    '59


    Dim tmpAge As Integer       'Simple DateDiff w/o Birthday correction
    Dim BrthDayCorr As Boolean  'BirthDay Before or After date in question

    If (AsOf = -1) Then         'Check for (Optional Date to Check against)
        AsOf = Date             'If Not Supplied, Assume Today
    End If

    tmpAge = DateDiff("YYYY", Dob, AsOf)        'Just the Years considering Jan 1, Mam
    BrthDayCorr = DateSerial(Year(AsOf), Month(Dob), Day(Dob)) > AsOf   'Check This Year

    basDOB2Age = tmpAge + BrthDayCorr           'Just Years and Correction
    
End Function

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over