hello

in my table DOA(date of appointment),DOR(date of retirement) field
is there now i want to calculate Quaffing service like no of years,
no of month, no of days completed so how I should write queries or
function please explain me step by step


Mohamed Mohiddin
Officer

__________________
Mohamed Mohiddin
Officer

This is a broad area that depends on many variables that you have not provided. If you wanted to know how to query on people with greater than 5 years of service, a solution can be provided. I would begin by looking at the help on the DateDiff function. Specific syntax varies with whether you are working from recordset field values, variables or control contents.

In order to get started, in the immediate pane of the debug window, pressing 'Enter' after the following:

?datediff("m","March 01, 2004","January 3, 2001")

yeilds:

-38

You can also simply subtract one date value, as indicated by the delimiters below, again in the immediated window, from another to return the number of days:

?#March 01, 2004# - #January 3, 2001#
 1153

To get Years, Months and Days is a bit more complex as you need to subtract successive intervals from the difference. ie, 32 months is 2 years worth of months less than 32 months being 2 years and (32 months - 12 months/year * 2 years = 32 - 24 = 8) 8 months. The same and more is required for days in order to account for month and leap year lengths.

Ciao
Jürgen Welz
Edmonton AB Canada
[email protected]