Results 1 to 5 of 5
  1. #1
    Lounger
    Join Date
    Mar 2001
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Calculating Age (Access 2000)

    I need an EASY way to calculate age in Access 2000, either in a query (preferred) or on a report (not preferred but necessary). I have a DOB field (date of birth configured as date/time).

  2. #2
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Wetherby, Yorkshire, England
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calculating Age (Access 2000)

    If you don't want it exact to the day then your query needs a calculated field ,say, AGENow()-[DOB])/365. Assuming your field is called[DOB] and you want your new field to be called [AGE]. This should give you the age in years to 2 (or more) decimal places (i haven't actually tried it but I'm pretty sure).
    Peter Herworth

  3. #3
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Richland, Washington, USA
    Posts
    407
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calculating Age (Access 2000)

    If you need just the years-old (not months, days, etc.) then try something like:
    <font face="Georgia">
    SELECT Names.Name, Names.DOB, Year(Date())-Year([DOB])+((Month(Date())<=Month([DOB])) And (Day(Date())<Day([DOB]))) AS Age
    FROM [Names];
    </font face=georgia>
    Easy? You decide.

  4. #4
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Columbus, Ohio, USA
    Posts
    286
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calculating Age (Access 2000)

    These work for me in calculating age in years and months. HTH.

    Function AgeInYears(Bdate As Long)
    Dim age
    If IsNull(Bdate) Or Bdate > Now Then
    age = Null
    Exit Function
    End If

    AgeInYears = Year(Now) - Year(Bdate) + (DateSerial(Year(Now), _
    Month(Bdate), Day(Bdate)) > Now)

    End Function

    Function AgeInMonths(Bdate As Long)
    Dim age
    Dim AgeTemp
    If IsNull(Bdate) Or Bdate > Now Then
    age = Null
    Exit Function
    End If

    AgeTemp = DateDiff("m", Bdate, Now) + (Day(Bdate) > Day(Now))
    AgeTemp = AgeTemp / 12 - Int(AgeTemp / 12)
    AgeInMonths = Format(AgeTemp * 12, "##")

    End Function


    Select * from table
    ORDER BY Ageinyears([dtmBirthdate]), AgeInMonths([dtmBirthdate]);

  5. #5
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Tennessee
    Posts
    194
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calculating Age (Access 2000)

    you can use this in a query

    age: DateDiff("yyyy", [BirthDate], Now()) + Int(Format(Now(), "mmdd") < Format([BirthDate], "mmdd"))
    or in a form

    textbox = DateDiff("yyyy", [BirthDate], Now()) + Int(Format(Now(), "mmdd") < Format([BirthDate], "mmdd"))
    it will give the age in whole years

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •