Microsoft Access

Calculating Age using Date of Birth

Creating field in form

To add a field that will calculate a customers age using their date of birth:

  1. Open up the form you wish to add the field.
  2. Under the HOME tab, click on the “View” drop-down option located in the ribbon on the far left and change to “Design View”.
  3. Insert a “text” box by clicking on the image that looks like this:      ab|Access Design Menu
  4.  Click and drag the text box on your form.
  5.  Open up the “Properties” tab if it’s not open already.

Access Properties Menu

  6. Under the Data tab of the Properties window, paste the following code under the “Control Source” row.

=Diff2Dates(“ym”,[ClientDOB],Now())

     7.  You will need to change the name in the brackets to match the name of your Date of Birth field.
     8.  Make a label for your field by clicking on the button (see ribbon image above) that looks like this:   Aa
     9.  Click and drag your label box on the form. Complete to your liking.
Your new field will look like this:
Access DOB Calc

Creating Field in Query:

1. Open up your Query. Make sure the Date of Birth field is included in the query.

2. Click on the “View” drop-down and choose “Design View”.

3. Insert a column.

4. In the “field” row under the new column, paste the following.

=Diff2Dates(“ym”,[ClientDOB],Now())

5. It will create the expression. You can change the name of the Expression name. See below for example:

Expr1: Diff2Dates(“ym”,[ClientDOB],Now())

——You can change it to something that you will recognize:

DOBCalc: Diff2Dates(“ym”,[ClientDOB],Now())

By Rana Kory

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s