calculating dates
Databases are great not only for storing
data, but also for performing
calculations on that data. For example,
you can calculate the number of years it
has been since a particular date in your
database. This could be used to find
someone's age, the number of years they
have been employed at the company, or if
their account is up for renewal.
Your first instinct might be to create a
new field that subtracts the recorded
date from today's date. While this is a
possible solution, there are two reasons
it isn't the best solution.
The result is not readily useful and may
be inaccurate.
The result is stored in the table and
does not update easily. Stored values
take up space and have to be updated
manually if the values change.
This article will show you a better way
to find the number of years from a date
in the database.
Find the number of days
Before beginning, create or open a query
that returns the fields with the dates
you want to use. We'll enter the
expression as a field in the query.
The first part of the calculation finds
the number of days between the recorded
date and the current date. To enter the
expression, open the query in Design
view. You may have to insert a new
column for the expression field.
To find the number of days between the
current date and another date, enter the
following in the Field row of an empty
column:
Result: (Date()[FieldName])
Replace Result with what you want the
name of the field to be, and FieldName
with the name of the field that contains
the date you want to calculate from. For
example, the Age column in the example
below was used to calculate the age
using the DOB field. The expression
entered in the field looks like this:
Age: (Date()[DOB])
When you've entered the expression,
click the Run button on the toolbar to
view the results. The query displays the
number of days between the current date
and the recorded date.
The example on the right shows the
values returned from the expression in
the Age and YearsEmployed fields.
Changing days into years
Once the days are calculated, turn the
value into a more meaningful result by
showing years instead of days.
Open the query in Design view, then
click the cell that contains the
expression. You can make it easier to
view and work with the expression by
using the Zoom window: press <Shift> +
<F2>, and the contents of the cell open
in a separate dialog box, as shown on
below.
To view the expression in years rather
than days, we have to divide the
expression by 365.25, the number of days
in a year (accounting for leap years).
Your expression should read like this:
(Date()[FieldName])/365.25
To see the results in years, click the
Run button to run the query again. The
expression is returned in years,
although there are a lot of decimals in
the returned value as shown on the
right.
Viewing the value as an integer
You may think that the easiest way to
make the returned value easier to use is
by rounding it off. However, this would
not be very accurate. For example, as
shown in the example below, Darlene Clay
is 45.8507871321 years old. If we
rounded that value up, she would be
listed as 46 years old, even though she
hasn't had her 46th birthday yet.
The best way to display the result is to
add the Integer function to the
expression. This returns only the
integer portion of the result, and the
decimals are left out completely.
Therefore, Darlene Clay's age of
45.8507871321 would appear as 45 until
the day of her birthday.
To add the Integer function, go back to
Design view. Click the cell with the
expression, and open the Zoom window, if
you want. The third and final addition
to the expression appears as follows:
INT((Date()[FieldName])/365.25)
When you've appended the expression, run
the query again. Only the integer values
are returned and decimals are hidden as
shown on the right.
