Hi Expert
In my table there are DOE(Date of Entry) and DOR(Date of Retirement)
now I want Total length of service like how many years, how may month
and how many days are completed. So please explain me


Mohamed Mohiddin
Officer

__________________
Mohamed Mohiddin
Officer

days ==> DateDiff("d",[DOE],[DOR])
weeks ==> DateDiff("w",[DOE],[DOR])
months ==> DateDiff("m",[DOE],[DOR])
years ==> DateDiff("yyyy",[DOE],[DOR])

"Life is a Database"

hi sdilucca
thanks for your replay but i want if days>30 month should one month and month>12 year should add one year like below i given manualy format
                      completed days month year
 DOE=15/05/1975
 DOR=29/07/2005
remaining days,month,year 16 00 00
                                  00 07 00
1976 to 2004 00 00 28
01/01/2005 to 29/07/2005 29 06 00
                                  --------------
          Total 45 13 28
if days >30 45-30 and month shold come 13+1
and month >12 14-12 and year should add one
like 28+1
so completed days month years
              15 02 29
will come if above example how should i write the
function and code
thanks once again

Mohamed Mohiddin
Officer

are you using VBA in your database?

"Life is a Database"

forget about VBA... I figured out the Query... What is the name of your TABLE and date FIELDS?

"Life is a Database"

ok.... I will assume the following table and fields:
  Table1
  dte_Start
  dte_End

Here is the SQL code for the query:

SELECT Table1.dte_start, Table1.dte_end, Int((DateDiff("d",[dte_start],[dte_end]))/365.25) AS Years, Int(((DateDiff("d",[dte_start],[dte_end])/365.25-1)-Int((DateDiff("d",[dte_start],[dte_end])/365.25-1)))*12) AS Months, Int((((DateDiff("d",[dte_start],[dte_end])/365.25-1)-Int((DateDiff("d",[dte_start],[dte_end])/365.25-1)))*12-Int(((DateDiff("d",[dte_start],[dte_end])/365.25-1)-Int((DateDiff("d",[dte_start],[dte_end])/365.25-1)))*12))*30)+1 AS Days
FROM Table1;

Now just replace the table and fields with your specific names. Let me know how it works out.

"Life is a Database"