VBA Code for Age at Date of Death

[icebergg]

I am working on a historical database that needs a unique age function:
the person's age calculated from a birthdate and date of death field. Can anyone help me get an exact age at the time of death? The age functions I've found here aren't helping me.

My fields are: Birthdate and Death

I would like to put the result in a form or use it in a report.

Thanks,
Icebergg

 

[katho]

dear icebergg,

try the following function CalcAge:

'first, calculate the year difference between birthdate and
'date of death.
' next, calculate if day and month of deathdate are bigger
' than the ones of the death date: if so, age = age, else
' (boolean value) age = age - 1

Public Function CalcAge(dtmBirthDate as Date, dtmDeathDate as Date)

Dim intAge As Integer

intAge = DateDiff("yyyy", dtmBirthDate, dtmDeathDate)

CalcAge = intAge + (dtmDeathDate < DateAdd(&quot;yyyy&quot;, intAge , dtmBirthDate))

End Function

This should give you the correct result about the age of a person.

Greetings from Belgium,

Vincent.

 

[KenReay]

Hi

I do not have my trusty Age function to hand since I am not in my office, but if you have an Age function which works, then it must be calculating age based on date of birth and (say) present date (ie Date())), so you simply pass two dates to the function, birthdate and date of death and substitute teh date of death for Date(), so

Age = CalcAge(DOB, DOD)

Public Function CalcAge(DOB As Date, DOD as Date) As Integer
... etc which ever method you have
End Function

I do not understand your problem? Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[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

Searching for employment in all the wrong places

 

[icebergg]

THANKS FOR THE FAST REPLIES! I am new to VBA but I love it!

I read them all and used the CalcAge function and put it inside an IIf as Kramerica suggested in another age post in this forum. I have learned so much in just the few days since I found Tek-Tips.
Here is my final solution as entered in my form and report:

=IIf(IsNull([DeathDate]),&quot;&quot;,IIf(IsNull([BirthDate]),&quot;&quot;,CalcAge([BirthDate],[DeathDate])))

It works beautifully where I have missing dates in either place.

Icebergg