Query to calculate years and months in date range

[MCLR]

Good day to all: I found your website a couple of days ago as I was searching for resources that can help within my particular problem in writing a script for an Access Query. The issue is to calculate a month and year situation. What I need is to create a query that will pull up how many months and years an employee has been working at a (now) date. For example: Employee A as been employed 2 years and 4 months and Employee B as been employed 7 months. I've found a good script for the year calculation, but for the script for the months is what I have trouble writing. What I am getting is these types of results 60 months = 5 years or 1 and 0 which I am not looking for. I am really a novice in writing scripts and I will really appreciate all the help I can get. Thanks MCLR

[MMcCarthy]

Good day to all: I found your website a couple of days ago as I was searching for resources that can help within my particular problem in writing a script for an Access Query. The issue is to calculate a month and year situation. What I need is to create a query that will pull up how many months and years an employee has been working at a (now) date. For example: Employee A as been employed 2 years and 4 months and Employee B as been employed 7 months. I've found a good script for the year calculation, but for the script for the months is what I have trouble writing. What I am getting is these types of results 60 months = 5 years or 1 and 0 which I am not looking for. I am really a novice in writing scripts and I will really appreciate all the help I can get. Thanks MCLR

I've had to guess at the field and table names but the following should work for you.

SELECT EmployeeID, EmployeeName, Int(DateDiff("m",[StartDate],Now())/12) AS Years, DateDiff("m",[StartDate],Now()) Mod 12 AS Months
FROM Employees;
 

[MCLR]

I've had to guess at the field and table names but the following should work for you.

SELECT EmployeeID, EmployeeName, Int(DateDiff("m",[StartDate],Now())/12) AS Years, DateDiff("m",[StartDate],Now()) Mod 12 AS Months
FROM Employees;
 

I just tested the script on the query that you gave me and it does work!!! I really want to thank you for all your help and to let you know that with help like this, I do learn a lot about script writing. Again thanks, MCLR

[MMcCarthy]

I just tested the script on the query that you gave me and it does work!!! I really want to thank you for all your help and to let you know that with help like this, I do learn a lot about script writing. Again thanks, MCLR

You're welcome.

[IPW133]

That is absolutely Brilliant. Much shorter than the script I've been using.