By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,456 Members | 1,390 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,456 IT Pros & Developers. It's quick & easy.

Calculate an age

P: 16
Hi i have already asked what to use for calculating an age and that works fine. The problem is it dosnt leave a value in the desired table.

Tables:
Employees & Holidays

I asked for a formula that would calculate time served with the company and to give a number in years. This part works except the value isnt shown in the table of employees it just stays blank? What can i do to solve this as the second part of the equasion is for the years that are returned corrispond to extra days holiday earned for long service to the company.

=DateDiff("yyyy",[Start Date],Now())+Int(Format(Now(),"mmdd")<Format([Start Date],"mmdd"))

This is the formula that i have pasted into the field "Length of Service" but no physical value is shown in the table.

If you can advise i would appreciate it.

Regards
Brian
ps only just learning this system so please be gentle lol
Dec 26 '07 #1
Share this Question
Share on Google+
4 Replies


FishVal
Expert 2.5K+
P: 2,653
What a very special reason you have to store it in a table?
Dec 26 '07 #2

jaxjagfan
Expert 100+
P: 254
It really shouldn't be a stored value since the employees' time with the company changes every day. It should be a calculated value that can be displayed in a form or a report. The value, if stored, would only reflect the date it was entered (stored) vice the difference between start date and today's date.
Dec 26 '07 #3

P: 16
I understand what your saying the only issue being is that i was going to use the value returned for another formual in the other form [Holidays] then add the formula to work out the amount of extra time earned. Am i making this hard for myself? should i be looking at this from another angle?

I need the result in years for the age or [Length of Service] then a formula to work out the extra holiday earned e.g

3yrs = 1 extra day
4yrs = 2 extra days

Should i look at moving this field into the holiday form itself? Sorry to be a pest just trying to get this right and i must say have really enjoyed the challange. I realise its only a small database but have found i would like to learn more as these programes are very versitile.

Thank you for your help

Brian
Dec 26 '07 #4

jaxjagfan
Expert 100+
P: 254
I understand what your saying the only issue being is that i was going to use the value returned for another formual in the other form [Holidays] then add the formula to work out the amount of extra time earned. Am i making this hard for myself? should i be looking at this from another angle?

I need the result in years for the age or [Length of Service] then a formula to work out the extra holiday earned e.g

3yrs = 1 extra day
4yrs = 2 extra days

Should i look at moving this field into the holiday form itself? Sorry to be a pest just trying to get this right and i must say have really enjoyed the challange. I realise its only a small database but have found i would like to learn more as these programes are very versitile.

Thank you for your help

Brian
Use the working formula for LengthofService instead of the value returned within the new formula. Logically: A + B = DesiredVal then A + (C+D) = DesiredVal if B = C+D.

If you store the value you will need to run an update query each time before you run the report to ensure you are reporting the correct "extra days". If you use a formula (showing the difference between start date and today's date) to get the LengthofService then it will be dynamic and no need to do updates before report is generated.
Dec 26 '07 #5

Post your reply

Sign in to post your reply or Sign up for a free account.