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
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
|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
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
||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!