Calculate Age

[Compositor]

I am trying to get a data access page to calculate an age in YEARS and MONTHS from a date of birth held in a field of the same name. I have managed to do this on a form but I am having real trouble achiveing the same results on a data access page. Can anyone point me in the right direction?

Thanks

 

[fneily]

Create your Data Access Page (DAP). Add a unbound textbox control. Bring up the property sheet and click on the ALL tab. You'll see Control Source, just like a form. Place your calculation in the box next to Control Source. Such as:

DateDiff("yyyy",[DOB],Date())+(Date()<DateSerial(Year(Date()),Month([DOB]),Day([DOB])))

DOB is a field in the table holding birthdate. When you go record to record, the age will be calculated.

When you post a problem, it would help to explain the error or actual problem, what functions you are using. You may be using a function not supported in DAP's. THEY ARE NOT FORMS. Most Access functions do not work in DAP's.

 

[MichaelRed]

Public Function basDOB2AgeExt(DOB As Date, Optional AsOf As Date = -1) As String


    'Michael Red    5/23/02
    '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

    '? 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 (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
    DtCorr = DateSerial(Year(AsOf), Month(DOB), Day(DOB)) > AsOf

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

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

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

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

End Function

Of course, this could have been discovered directly in thread701-498444 using advanced search ...



MichaelRed