Comparing date fields in Access

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm trying to calculate yrs of service for employees. On a report, I've set
up a control, labeled DaysCalc, with the expression =Date()-[DateHired].
This correctly returns the number of days since hire date. In another
control, labeled "Years of Service", I've entered the following control
source =IIf([DaysCalc]<"365","0",[DaysCalc]/"365"). Out of 15
employees, only 3 return with incorrect values. Any suggestions?
 
Since DaysCalc is a numeric value, remove the quotes from around 365.

However, since there aren't exactly 365 days in a year, a better way to
compute years of service is:

DateDiff("yyyy", [DateHired], Date()) - _
IIf(Format(Date(), "mmdd") < Format([DateHired], "mmdd"), 1, 0)
 
Back
Top