Isolation of problem with calculating age 1

[MADDPM]

In a recent post of 11-Nov-2008, Michael Red provided the code for calculating age from "date of birth" to "date of death or current date".

After finally placing the code in the correct module with the correct code for calling the function, I think I've isolated the last annoying problem.

In correlation with my customized function code below:

1) The code will display #Error if neither date is supplied in the underlying table - the way it should, no problem

2) When using the call: =basDOB2AgeExt([DOB]), the code will display age from the date of birth (DOB) to the present time. Okay for some situations, but not my particular situation.

3) When using the call: =basDOB2AgeExt([DOB],[DOD]) there are two possible results:
a) If a date of death (DOD) *is* provided by the underlying table, age is nicely, precisely provided.
b) If no date of death (DOD) is provided by the underlying table, #Error is displayed in the control. Not so good.

I'm confused as to what the "If" code in the function is doing. To me, the code appears as if - if [DOD] = -1 (true), then it should = Date. But shouldn't it be if [DOD] = 0 (false), then it should = Date?

Any advice will be greatly appreciated.

Here's my customized code:
Public Function basDOB2AgeExt(DOB As Date, Optional DOD As Date = -1) As String

'Michael Red 5/23/02
'DOB is just the date of Birth
'DOD is an optional date to check - as in examples 1 & 2
'Otherwise, the DOB is checked against the Current Date

'? basDOB2AgeExt(#8/21/42#)
'59 Years 9 Months and 2 Days.

'? basDOB2AgeExt(#8/21/1942#, #8/20/2022#)
'79 Years 11 Months and 30 Days.

'? basDOB2AgeExt(#8/21/1942#, #8/21/2022#)
'80 Years 0 Months and 0 Days.

Dim tmpAge As Integer 'Simple DateDiff w/o Birthday correction
Dim tmpDt As Date 'Date to use in intermediate Calcs
Dim DtCorr As Boolean 'BirthDay Before or After date in question
Dim YrsAge As Integer
Dim MnthsAge As Integer 'Additional Mnths
Dim DaysAge As Integer 'Additional Days

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

tmpAge = DateDiff("YYYY", DOB, DOD) 'Just the Years considering Jan 1, Mam
DtCorr = DateSerial(Year(DOD), Month(DOB), Day(DOB)) > DOD

YrsAge = tmpAge + DtCorr 'Just Years and Correction
tmpDt = DateAdd("yyyy", YrsAge, DOB)

MnthsAge = DateDiff("m", tmpDt, DOD)
DtCorr = DateAdd("m", MnthsAge, tmpDt) > DOD
MnthsAge = MnthsAge + DtCorr

tmpDt = DateAdd("m", MnthsAge, tmpDt)
DaysAge = DateDiff("d", tmpDt, DOD)

basDOB2AgeExt = YrsAge & " Years " & MnthsAge & " Months and " & DaysAge & " Days."

End Function

randy700 (Programmer)

Called by:
=basDOB2AgeExt([DOB]) 'no DOD value passed, use current date.
=basDOB2AgeExt([DOB],[DOD]) 'calculate using DOB and DOD values.

 

[traingamer]

If there is no [DOD] in your underlying table, do not call that function using [DOD]. It sounds like the [DOD] may be null in your instance which likely throws off the whole function.

Greg
People demand freedom of speech as a compensation for the freedom of thought which they seldom use. Kierkegaard

 

[Remou]

The -1 comes from the parameters:

Optional DOD As Date = -1

If you always wish to pass DOB,DOD you can use Nz to invoke the -1 case:

=basDOB2AgeExt([DOB],Nz([DOD],-1)

You may wish to provide for a null DOB.

 

[MADDPM]

Dearest, dearest Remou -

You are the best! It works perfectly - after adding the final ")".

Thank you so, so much!

I'll stop pestering everyone now . . .

 

[CasVan]

This code has been of great help.

I would like to get rid of the #Error code which is being produced by empty DOB fields. If there is no birthdate the field should be blank.

Is it something easy as adding an Nz or isnull to the following?

=basDOB2AgeExt([DOB],Nz([DOD],-1)

Any Ideas?

~Cas

 

[PHV]

=basDOB2AgeExt([DOB],Nz([DOD],-1)[!])[/!]

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[CasVan]

Thanks PH.. For answering, I actually posted the qoute without the extra ) by accident. I have the correct version in my database.

But the question still stands..

Is it something easy as adding an Nz or isnull to the following? in order to produce a blank field if the DOB field is empty

=basDOB2AgeExt([DOB],Nz([DOD],-1))

~Cas

 

[Remou]

You could alter the code, and hope that MichaelRed does not find out

If IsNull(DOB) Then  
    basDOB2AgeExt = Null
Else

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

    tmpAge = DateDiff("YYYY", DOB, DOD)        'Just the Years considering Jan 1, Mam
    DtCorr = DateSerial(Year(DOD), Month(DOB), Day(DOB)) > DOD

    YrsAge = tmpAge + DtCorr           'Just Years and Correction
    tmpDt = DateAdd("yyyy", YrsAge, DOB)

    MnthsAge = DateDiff("m", tmpDt, DOD)
    DtCorr = DateAdd("m", MnthsAge, tmpDt) > DOD
    MnthsAge = MnthsAge + DtCorr

    tmpDt = DateAdd("m", MnthsAge, tmpDt)
    DaysAge = DateDiff("d", tmpDt, DOD)

    basDOB2AgeExt = YrsAge & " Years " & MnthsAge & " Months and " & DaysAge & " Days."
End If

 

[MichaelRed]

I really do not care about those who 'modify' any code I have posted. It is all in the nature of unfinished lessons / examples, not intended to be production quality code.

That being clear, it seems reasonable -to me- that if the attempt is to calculate the 'age' of something it needs to have both the starting AND ending dates to do the calculation. Thus it is QUITE reasonable to display "#ERROR" when these values are not provided.

To provide the dummy entry for even the the ending date can obvioously in some interesting variations in the data from day to day ... i.e. born today may show different age netween today and tomorrow? ...



MichaelRed

 

[CasVan]

Remou/MichaelRed -

Thanks for the quick response.

This is a great code.. I am using it in the genealogy database I am slowly building. Due to the nature of history I find I have a need input various DOB,DOD dates into the system by record type. So I was quite HAPPY to find a module that could account for living ages, and the age a person who has passed away and present it in format I could utilize.

Remou- I tried your variation and I still had the #error message on the effect fields.

The #Error message was giving me an query error message "Data Type mismatch in criteria expression" in the affected field. When I went into the underlining DOB query an indicated is not null in the DOB field. The AgeGrp query ran fine. In theory I would like to account for the individuals without DOBs on the AgeGrouping report with either a null value and/or maybe a dummy date of 00/00/0000. Now this might not be possible. But to be on the safe side I thought I would investigate these options.

Cas

 

[Remou]

I tested, and did not get an error. What is your query sql? What code are you using?

 

[Remou]

Hmm. Best reply in a new thread referencing this one or MADDPM will be snowed under with unnecessary posts.

 

[MADDPM]

Actually, I'm quite interested myself in the solution.

I have employees who, due to their "on-call" status, are not calculated for "retirement eligibility" - so I do not enter a DOB.

My work around on both forms and reports, was to use conditional formatting - if the value Is Null - text is the same color as the background of the control and therefore isn't visible.

I'll keep an eye out for new thread.

Many thanks-Colleen