Calculating age from a birth date in Access

Applies to
Microsoft Office Access 2003
Microsoft Access 97, 2000, and 2002

In Access, you can calculate a person's age, in years, by using an expression. For example, you can enter a birth date in a control on a form and display the number of elapsed years from that date in another control. The following steps demonstrate this technique by creating a form, inserting two text boxes, and entering an expression.

  1. In the Database window, click Forms under Objects, and then click New.
  2. In the New Form dialog box, click Design View, and then click OK.
  3. Using the Text Box tool in the toolbox, add two unbound text boxes to your form.
  4. Set the Name property for one text box to Birthdate and the other to Age.
  5. Set the Format property for the Birthdate text box to Short Date.
  6. Set the ControlSource property for the Age text box to the following expression:
=DateDiff("yyyy", [Birthdate], Now())+ Int( Format(now(), "mmdd") < Format( [Birthdate], "mmdd") )
  1. Switch to Form view.
  2. In the Birthdate text box, type a date (mm/dd/yyyy), and then press the TAB key.

The value displayed in the Age text box is the number of elapsed years from the birth date.

Applies to:
Access 2003