Calculate length of years, months and days in Access 2003

5 pts.
Tags:
Microsoft Access 2003

Dear sir,

I own my business in different cities. My employs keep on transferring from one city to the other.

I want to calculate length "years, months and days" of employs in a city and then calculate total service an employ in all the cities. Answer should be in "years, months and days".

Thanks!

Thanks. We'll let you know when a new response is added.

To calculate length of years, months and days in Access 2003, you need
to create a function. The function will use a begin date and an end date
to return a value in the units you have specified. Use the Age()
function to calculate an age in years for a begin date as the person’s
date of birth and the end date of your choice. The current date is the
most commonly used end date, although you may desire to use a date of
death as the end date or another date that makes sense to your task.
Input the “StartDate” and use varAge = DateDiff(“yyyy”, varBirthDate,
Now) if you want the current date as the end date.

Discuss This Question: 3 Replies

Thanks. We'll let you know when a new response is added.
• Hi,
The very first constraints that are required are two dates: -
1. Date Obtained
2. Date of Expiry

The above dates are available in the first two columns.

Column 3: - Years: DateDiff("yyyy",[DateObtained],[DateOfExpiry])

Column 4: -
Months: DateDiff("m",[DateObtained],[DateOfExpiry])

Column 5: -
Days:

The code can be implemented as: -

```SELECT TestDate.DateObtained, TestDate.DateExpired,
DateDiff("yyyy",[DateObtained],[DateExpired]) AS Years,
IIf(Day([DateObtained])<=Day([DateExpired]),DateDiff("m",[DateObtained],[DateExpired])-[Years]*12,DateDiff("m",[DateObtained],[DateExpired])-[Years]*12-1)
AS Months,
• `=DateDiff("yyyy",[DOJ],Date())+Int(Format(Date(),"mmdd")<Format([DOJ],"mmdd")) & " Years, " & (DateDiff("m",[DOJ],Date()))-((DateDiff("yyyy",[DOJ],Date())+Int(Format(Date(),"mmdd")<Format([DOJ],"mmdd")))*12) & " Months"`