429,189 Members | 2,167 Online
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,189 IT Pros & Developers. It's quick & easy.

# Query to calculate years and months in date range

 P: 2 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 Jan 24 '07 #1
4 Replies

 Expert Mod 10K+ P: 14,534 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. Expand|Select|Wrap|Line Numbers SELECT EmployeeID, EmployeeName, Int(DateDiff("m",[StartDate],Now())/12) AS Years, DateDiff("m",[StartDate],Now()) Mod 12 AS Months FROM Employees;   Jan 24 '07 #2

 P: 2 I've had to guess at the field and table names but the following should work for you. Expand|Select|Wrap|Line Numbers 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 Jan 24 '07 #3

 Expert Mod 10K+ P: 14,534 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. Jan 24 '07 #4

 P: 1 That is absolutely Brilliant. Much shorter than the script I've been using. Sep 1 '10 #5