• Access Index
• Printer friendly version
• Related Topics:
  Calculating in Queries
  Working with Dates

Working out Someone's Age

When storing personal data, it is normal practice to record a person's date of birth rather than their age. This is for the simple reason that the date of birth never changes, but the age will be different next year. If you need to show someone's age you should ask Access to calculate it for you.

Calculating someone's age from their birthday is not quite as simple as it might at first seem! Of course, you take the current year and subtract it from the year in which they were born. This will give you an accurate age in years - providing they have had their birthday this year. So, to be absolutely certain you need to decide if the day and month of their birthday has passed yet.

If it has you subtract their birth year from the current year. If not, then you subtract their birth year from the current year, and then subtract 1 from the answer. A simple calculation we do in our heads without even thinking about it, but quite a task to explain to Access. To get Access to do it this way requires a number of conditional statements, resulting in a very complicated formula! (Conditional statements are easier in Excel that in Access. Take a look at the example in the tutorial Working Out a Person's Age in Excel).

Fortunately, there are a couple of easier ways. Providing you understand the results they provide and use them accordingly, they should suit most purposes. Both examples below take birth dates from a field called DoB and calculate an age, which is displayed in a new field called Age.

To Calculate Approximate Age

Use this expression...   Age: Year(Now( ))-Year([DoB])

Age: creates a new field called Age Year(Now( )) calculates the year number from the current year Year([DoB]) calculates the year number from the date it finds in the [DoB] field The expression subtracts the person's birth year from the current year to give an approximate age, but does not take account of whether or not thy have had their birthday. It will tell you how old they will be this year.

To Calculate Accurate Age

Use this expression...   Age: (Date()-([DoB])/365.25

 It makes use of the fact that Access uses serial numbers to store dates

 

Date( ) calculates today's date The expression subtracts the person's birth date serial number (which it finds in the field [DoB]) from today's serial number, leaving the number of days in between. It then divides that number by 365.25 (being the average number of days in a year) to give their age in years. In this example the field has been formatted to show two decimal places.

Applying the Technique

You can use these expressions to calculate someone's age on a particular date, and refine the query by adding criteria to display only the records for people whose ages fall within a particular range

This example creates a new field called Age and calculates the person's age on a particular date, which it finds in a field called Chosen Date. It then uses the expression <35 in the criteria row to display only the results that calculate to less than 35. The result is a list of people who were under 35 years of age on the date in question.

If you want to calculate people's ages on a particular date, providing that it is the same date for everyone, you don't need to retrieve that date from a field. Simply include it in the expression…

Age: (#01/11/65#-[DoB])/365.25 would create a new field called Age and calculate how old the people were on 1st November 1965. Note: If the person's birth date is later than the date used in the expression (i.e. they had not been born by that date) their age would show as a minus number!