none
Access Table- Calcuate Age using DOB RRS feed

  • Question


  • I have a table called "Contacts" and need to find out how to calculate the Age based on the Date of Birth.  The field names are:

    DOB      (Date/Time formatting)

    Age      (the field that needs to be calculated)

    Wednesday, January 16, 2019 7:19 PM

All replies

  • Hi,

    There are (at least) a couple of approaches to calculating Age from DOB.

    1. Age: DateDiff("yyyy", [DOB], Date()) + (Format(Date(),"yyyymmdd")>Format([DOB],"yyyymmdd"))

    2. Or use this function

    Wednesday, January 16, 2019 7:49 PM
  • and avoid using a calculated field in a table, please.

    peter n roth - http://PNR1.com, Maybe some useful stuff


    • Edited by Peter N Roth Wednesday, January 16, 2019 8:39 PM incomplete expression
    Wednesday, January 16, 2019 8:38 PM
  • I too, avoid calculated fields.

    As for calculating the person's age, refer to http://www.devhut.net/2010/06/22/ms-access-calculate-the-age/


    Daniel Pineault, 2010-2018 Microsoft MVP
    Professional Support: http://www.cardaconsultants.com
    MS Access Tips and Code Samples: http://www.devhut.net

    Wednesday, January 16, 2019 10:19 PM
  • You might like to take a look at Age.zip in my public databases folder at:

    https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.  

    If you have difficulty opening the link, copy the link (NB, not the link location) and paste it into your browser's address bar.

    This little demo file illustrates the use of a number of age functions, and caters for a DoB being on the 29 February in a leap year and the date at which the age is computed being on 28 February in a non-leap year, a scenario in which a simple expression would return an incorrect result.

    Ken Sheridan, Stafford, England


    • Edited by Ken Sheridan Wednesday, January 16, 2019 11:07 PM Typo corrected.
    Wednesday, January 16, 2019 11:05 PM