Adding Date Difference

[AminSubhani]

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". Moreover, the present stay of an employ must be continued i.e with each passing day it should automatically add on.
Thanks

AminSubhani

Required OUTPUT

Name	City	From	To	Service	Total Service
Mansoor	Lahore	1 Jan 2007	31 Dec 2007	1 year 0 months 0 Days
Mansoor	Multan	1 Jan 2008	15 Apr 2010	2 Years 3 Months 15 Days
* Mansoor	Faisal Abad	16 Apr 10	& 12 Nov 15 (Continue)	5 Years 6 Months 27 days	8 Years 10 Months 12 Days
* The last entry should be based on current date (&) i.e today is 12 Nov 15 and answer is 5 Years 6 Months 27 days with Total Service of 8 Years 10 Months 12 Days but tomorrow i.e on 13 Nov 15 it should be automatically 5 Years 6 Months 28 days with Total Service of 8 Years 10 Months 13 Days and so on.

[orange]

Why not simply use DaysInEmploy? Why Years, months and days?
If you need to format it for display, then you could have a routine to do so.

Bob 2312 days
Jim 345 days

If you know startDate and EndDate, you could use DateDiff.
Just a thought for consideration.

[CJ_London]

the calculation for years/months/days is a common request and is handled in a UDF (i.e. written in vba code) - here is an example

https://bytes.com/topic/access/answe...etween-2-dates

but google/bing 'vba years,months,days' or similar to find more

with regards your query, you have shown the result you want but not the underlying data that will be used to populate it. Please provide some sample data. Note that Name, From, To are all reserved words and using them as field names will only cause problems, so if these really are your field names, change them

[ssanfu]

Here is a query that seems to return the y,m,d :

Of course, you have to change the table name and field names to match yours. To get the total, I would use a totals query or use grouping in a report.

SELECT tblAminSubhani.EmpName, tblAminSubhani.City, tblAminSubhani.startdate, tblAminSubhani.EndDate, DateDiff("yyyy",[startdate],Nz([EndDate],Date())+1)-IIf(Format([StartDate],"mmdd")>Format(Nz([EndDate],Date()),"mmdd"),1,0) AS theYears, IIf(Day([startdate])<=Day(Nz([EndDate],Date())+1),DateDiff("m",[startdate],Nz([EndDate],Date())+1)-[theYears]*12,DateDiff("m",[startdate],Nz([EndDate],Date()+1))-[theYears]*12-1) AS theMonths, DateDiff("d",DateAdd("m",[theMonths],DateAdd("yyyy",[theYears],[startdate])),Nz([EndDate],Date())+1) AS theDays, [theyears] & " years, " & [themonths] & " months, " & [thedays] & " days" AS ToString
FROM tblAminSubhani;