Calculated Fields

[hoikhype]

I am looking to do a calculated field. Not sure what is the best way to do this, but I would like to have a dates in a table with all of our employees that has what date they started with the company. From that I want to know not how many days they have been with the company, but just the years. so if I have been with the company 364 days, it should only show up as 0, and if I have been with the company for 365, it will read 1. I would like this to be in a query somehow. We use the number of years they have been with the company for their pay, and need it to be years, and not days. Is anyone able to help on this or send an example of how this would be done? Any help is much appreciated.

 

[David R]

Calculated fields in a query will look something like

YearsOfService: Int((Now()-[HireDate])/365)

Then you can use YearsOfService in any form/query/report that references that query, and it will ALWAYS be up to date. Never store a calculated value if you can help it!

 

[Pat Hartman]

Now() includes time. It is best to use Date() when all you want is the date.

 

[David R]

Now() includes time. It is best to use Date() when all you want is the date.

Depends on how HireDate is stored, I suppose. I was going off of the statement that 364 days should be zero, so wanted to avoid the Midnight 'bug' from Date().

Int((Date()-#10/16/2012 8:00:00AM#)/365) = 0
Int((Now()-#10/16/2012 8:00:00AM#)/365) = 1

 

[David R]

Another way of looking at it that won't fall prey to leap years is to pull Month(), Day() and Year() and then compare them separately. I'll leave that as an exercise for the reader...

 

[Pat Hartman]

I forgot to mention that I would use the DateDiff() function which means you don't need to worry about leap years or the midnight "bug" since it gives you the difference in years. Just subtracting one date from another gives you the difference in days which you then have to convert to years. Using the DateDiff() function allows you to specify the unit of measure so you get years and the function is coded to correctly handle leap years.

 

[David R]

DateDiff is best. That's why you're the Super and I'm apparently still a Newly Registered user after 12 years, Pat.

 

[Pat Hartman]

You're so funny I needed that laugh. I'm in data h*** and Access hates me. I'm gunna take my ball and go home.

 

[hoikhype]

Calculated fields in a query will look something like

YearsOfService: Int((Now()-[HireDate])/365)

Then you can use YearsOfService in any form/query/report that references that query, and it will ALWAYS be up to date. Never store a calculated value if you can help it!

Thanks! This works great, but it comes up with a number like 20.423092339439. How can I get it to not round, but just show the number 20? Any ideas?

 

[Pat Hartman]

Try reading my post # 6