hi all members i want urgent help
In my table doa(date of appointment ) and dor(date of retirement ) field now I want qualifying service from dor-dor like day, month, year eg:
Dor=31/03/2004
Doa=02/01/1973

qualifying service= day month year
Jan-1973 30 0 0
Feb-1973 to 31st Dec-1973 0 11 0
1st Jan-1974 31st Dec-2003 0 0 29
1st Jan-2004 31st Mar-2004 0 3 0
                -------------------------
        Total 30 14 29
Month is >12 year should 30 like
                Day month year
                                30 2 30
in above eg. How should write the date function


Mohamed Mohiddin
Officer

__________________
Mohamed Mohiddin
Officer

If I understand the problem correctly, you will be getting a total for days, months and years.

I would look at days, and if the number is greater than 30, divide the number by 30 and truncate the result to give the number of months, and the take the number of days % 30 to get the remaining days. You can then do the same thing with the months i.e. if greater than 12, divide by 12 truncate the fractional part of the answer, and add to years, and then use the mod function to determine the number of months remaining, or alternatively, subtract the number of new years added * 12 from the original number of months.

Mike
EchoVue.com