By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,189 Members | 2,167 Online
Bytes IT Community
+ 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
Share this Question
Share on Google+
4 Replies


MMcCarthy
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
  1. SELECT EmployeeID, EmployeeName, Int(DateDiff("m",[StartDate],Now())/12) AS Years, DateDiff("m",[StartDate],Now()) Mod 12 AS Months
  2. FROM Employees;
  3.  
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
  1. SELECT EmployeeID, EmployeeName, Int(DateDiff("m",[StartDate],Now())/12) AS Years, DateDiff("m",[StartDate],Now()) Mod 12 AS Months
  2. FROM Employees;
  3.  
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

MMcCarthy
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

Post your reply

Sign in to post your reply or Sign up for a free account.