DateDiff

  • Thread starter Thread starter Sam
  • Start date Start date
S

Sam

hi guys,
I got table called employees and in that table I have
amongst other fields a HireDate and YearsEmployed.
I would like to run a query or some other way and update
YearsEmployed at will for all employees based on HireDate.
I just need years in that YearsEmployed.
Thank you
 
hi guys,
I got table called employees and in that table I have
amongst other fields a HireDate and YearsEmployed.
I would like to run a query or some other way and update
YearsEmployed at will for all employees based on HireDate.
I just need years in that YearsEmployed.
Thank you
Click to expand...

Sam,

Why do you even have that [YearsEmployed] field in the table at all?
As you have found, it is out of date for at least one employee almost
every day.

As long as you have the HireDate field holding the date of employment,
whenever you need the years employed for any employee, compute it
based upon the Hire Date.

Here is a formula to compute the years employed when needed.

In a Query:
Age: DateDiff("yyyy", [HireDate], Date()) - IIF(Format([HireDate],
"mmdd") >Format(Date(), "mmdd"), 1, 0)

Directly as the control source of an unbound control:
=DateDiff("yyyy",[HireDate],Date())-IIf(Format([HireDate],"mmdd")>Format(Date(),
"mmdd"),1,0)

Do not save this value. Re-compute it when needed.
 
Thank you, that worked.
The reason that I have that field is because it does some
other things, like update annual and sick, etc...
-----Original Message-----
hi guys,
I got table called employees and in that table I have
amongst other fields a HireDate and YearsEmployed.
I would like to run a query or some other way and update
YearsEmployed at will for all employees based on HireDate.
I just need years in that YearsEmployed.
Thank you
Click to expand...

Sam,

Why do you even have that [YearsEmployed] field in the table at all?
As you have found, it is out of date for at least one employee almost
every day.

As long as you have the HireDate field holding the date of employment,
whenever you need the years employed for any employee, compute it
based upon the Hire Date.

Here is a formula to compute the years employed when needed.

In a Query:
Age: DateDiff("yyyy", [HireDate], Date()) - IIF(Format ([HireDate],
"mmdd") >Format(Date(), "mmdd"), 1, 0)

Directly as the control source of an unbound control:
=DateDiff("yyyy",[HireDate],Date())-IIf(Format ([HireDate],"mmdd")>Format(Date(),
"mmdd"),1,0)

Do not save this value. Re-compute it when needed.
--
Fred
Please only reply to this newsgroup.
I do not reply to personal email.
.
Click to expand...
 
Back
Top