Visit Applied Office's Website Contact  
 

June 2006 : access

 

  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.

 

  quick jump


Jump to other stories through these links:

[http://www.appliedoffice.net/news/2006-jun/news contents.htm]

  screen shots


The following screen shots illustrate the articles on the left.  Click on one for a larger view.