Looking for help with an Access 2003 expression that will allow me to
calculate the number of years and months an employee has been employed
by a company using their hire date and their start date.
From:
http://groups.google.com/group/microsoft.public.access/msg/cf857b8e3aa9c6b0
If [HireDate] is the earlier date (otherwise switch HireDate and
StartDate everywhere),
qryElapsedTime:
SELECT Int(Format([StartDate], 'yyyy.mmdd') -
Format([HireDate], 'yyyy.mmdd')) & ' year(s)/' & (12 +
Int(Format([StartDate], 'mm.dd') - Format([HireDate],
'mm.dd'))) Mod 12 & ' month(s)/' & Day([StartDate]) -
Day([HireDate]) + Abs(Day([HireDate]) > Day([StartDate])) *
Day(DateSerial(Year([HireDate]), Month([HireDate]) + 1, 0)) & ' day(s)' AS
ElapsedTime FROM tblAny;
tblAny
AID AutoNumber
HireDate Date/Time
StartDate Date/Time
AID HireDate StartDate
1 3/4/2002 4/2/2007
2 2/2/2003 4/3/2007
!qryElapsedTime:
ElapsedTime
5 year(s)/0 month(s)/29 day(s)
4 year(s)/2 month(s)/1 day(s)
If you decide to use that query, be sure to read the post cited and test
the query thoroughly. Also, I think someone, likely Douglas Steele,
posted an alternate solution to this problem.
James A. Fortune
(e-mail address removed)
