Calculating Age – Accounting for missing DOB’s

[CasVan]

All -

This ia a variation of the “Isolation of problem with calculating age” post by MADDPM of 13-Nov-2008

I would like to account for individuals without DOBs with either a null value and/or a dummy date of 00/00/0000. Right now, if the qryDates runs, the fields with the missing dates return with #Error and the "Data Type mismatch in criteria expression" error.

If I add is not null to the DOB field in the qryDates, the query runs without errors.

I have listed my query/module below; any helpful suggestions are gladly welcomed.

Query: QryDates

Issue: Age_YMDs: basDOB2AgeExt([DOB],NZ([DOD],-1))

SQLQuery:

SELECT qryFAMLists.FullnameFIRST, qryFAMLists.FullnameLAST, qryFAMLists.DOB, qryFAMLists.DOD, basAgeExt([DOB],NZ([DOD],-1)) AS Age, Format([DOB],'mmm') & '-' & Format([DOB],'dd') AS MnthDy, Year([DOB]) AS BYear, Year([DOD]) AS DYear, Month([DOB]) AS Mnth, Day([DOB]) AS Dy, Format([DOB],'mmmm') AS Mnth_, Format([DOB],'ddd') AS DyBorn_, AgeGroup([DOB],IIf(IsNull([DOD]),Date(),[DOD])) AS AgeGrp, basDOB2AgeExt([DOB],NZ([DOD],-1)) AS Age_YMDs, DateSerial(Year(Date()),Month([DOB]),Day([DOB])) AS BDay, qryFAMLists.Gender
FROM qryFAMLists;

Public Function basDOB2AgeExt2(DOB As Date, Optional DOD As Date = -1) As String

    'Michael Red    5/23/02
    'Customized by MADDPM 11/13/08
    '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 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
    

End Function
'Called by:
'=basDOB2AgeExt([DOB])        'no DOD value passed, use current date.
'=basDOB2AgeExt([DOB],[DOD])  'calculate using DOB and DOD values.
'*basDOB2AgeExt([DOB],NZ([DOD],-1)) If no DOD use current date

Enough rambling... Thoughts anyone?

~Cas

 

[MichaelRed]

At the risk of being foolishly contradictory, change DOB to an optional parameter as well (as well as the DOD parameter). also change the data type of both input arguments to 'Variant' (which should remove the need for the Nz function for either / both). If either is left blank, a small tweak should be able to rturn three blank strings or even "unknown" for all of them. Should preserve the concept of not returning FALSE (or ever changing) information and provide an easy to understand message about the lack of data to support the function.

In looking a bit at the overall query, I am somewhhat at a loss in understanding why you seem to require the query to reformat the dateparts of the two dates in multiple fields. you even call the age calculation function twice with the same arguments! This reminds me of the age old (or at least recent ages old) of some definition " ... asking the same question, expecting different answers?

Where ever the query results will be used should be capable of using the single values in any number of formats, thus reducing the redundncy in the query itself.

Assuming 'AgeGrp is a function somewhat based on partion, I would reduce the actual query to:

SELECT qryFAMLists.FullnameFIRST, qryFAMLists.FullnameLAST, qryFAMLists.DOB, qryFAMLists.DOD, 
basAgeExt([DOB],NZ([DOD],-1)) AS Age, 
qryFAMLists.Gender'
FROM qryFAMLists;

... and use the format function & AgeGrp function to present the pretty print versions of the two dates.







MichaelRed

 

[CasVan]

MichaelRed -

Thanks for the heads up on the redundancies featured in my query. I will try to streamline my query and keep the group updated on my issues... progress. Thanks Again

~Cas

 

[Remou]

CasVan, you can thank people for their help with a problem by clicking the link:

Thank MichaelRed
for this valuable post!