Comparing date fields in Access

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?
 
D

Douglas J. Steele

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)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Auto fill in text box using iif function 3
Date Difference 5
Two questions regarding manual input to calculated fields/reports 1
Years of Employment 2
DATE FIELDS IN REPORTS 5
Make query to calculate two dates 2
How can i update the experience date? 2
Number of days falling in various years 4

Top