# Using if or else in a form field to to Calculate number of years

1. Novice
Windows 10 Access 2016
Join Date
Jan 2021
Posts
4

## Using if or else in a form field to to Calculate number of years

I have a problem in calculating number of years pertaining to retirement of a teacher. The problem is as follows

ID
Name
Registered Date - Reg
Left - Left
Rejoined - Returned
Left again - Left2
Rejoined Again - Returned2
Retired - Retired

Service to date - =Int(DateDiff("yyyy",[Reg],[Left])+DateDiff("yyyy",[Returned],[Left2])+DateDiff("yyyy",[Returned2],Date())/365.25)

Full years of retirement - =Int(DateDiff("yyyy",[Reg],[Left])+DateDiff("yyyy",[Returned],[Left2])+DateDiff("yyyy",[Returned2],[Retired])/365.25)

Above codes are stipulated in from fields

Can you help me to get following answered.

How to use if or else to calculate

1. For Example if the teachers retire prior to the todays date how to get Total service years.

2. Full years of retirement

I think I get the correct answer for the 2nd. But if the teacher retire before the todays date I do not get the correct answer.

Thank you.

Wilfred.

'what you show
[#yrs]+[#yrs]+ ([#yrs] / 365.25)

or
([#yrs]+[#yrs]+ ([#yrs]) / 365.25

3. Novice
Windows 10 Access 2016
Join Date
Jan 2021
Posts
4
Thanks for the prompt reply. I may have to explain the problem. It is program I made for teachers of Sunday School.

Teachers get registered to the Sunday School with an application form. Teacher has to fill the registered date in the form. That date I have named as Reg, in the form field. Teachers are allowed to leave the school for taking of examinations, wedding etc. Left and Left2 are for leaving dates. Then when they come back as they are rejoining again I have given name as for the field as Returned. The period between Reg and Left is first served period in the school and when they leave for the second time and the period between returned and Left2 is a serving period etc. Then period between Returned2 and Retired is the final serving period. Some teachers never take leave. Some teachers leave for the fist time and never returns.

We have to add all these periods and get the total years served.

What we want to find is Serving period in years at a given date which we take as Date() and calculate the served period. If the teachers retire prior to the todays date, how to get Total service years because by then teacher has got retired.

Hope I am not troubling you.

Thank you.

Wilfred

4. I think that you db is normalized correctly. What if a teacher has more that two periods of leave?
BTW "Name" is a reserved word and should not be used as the name/title of any object (field, form, report etc)

5. I think Bob meant NOT normalised correctly.
Apart from the points already made there are several issues here. For example
1. If someone starts in say Jan 2018 and leaves in Dec 2018, rejoins in Feb 2019 and leaves again in Dec 2019, your expression would count those two combined portions as zero years when it should be 21 months (1 year 9 months)
2. A year isn't exactly 365.25 days. Whilst it probably won't affect years of service, it could conceivably cause discrepancies.

I recommend you treat each section use a modified age function which allows for part years correctly, then add them together before rounding.
There are numerous age function examples available online including several in this thread at another forum https://bytes.com/topic/access/answe...invalid-syntax
The one is usually use is by far the simplest
Code:
`DateDiff("yyyy", [DOB], Date) + (Format([DOB], "mmdd") > Format(Date, "mmdd"))`
Last edited by isladogs; 01-14-2021 at 01:12 PM.

I think Bob meant NOT normalised correctly.
Yes, indeed. Thank you Colin. As alert and eagle eyed as ever, I'm glad to see

7. Novice
Windows 10 Access 2016
Join Date
Jan 2021
Posts
4
Dear Mr. Collin,

Thanks for the reply. How pl let me know for for it. The above code not the correct answer to my problem. That is, if the teachers retire prior to the todays date, how to get Total service years because by then teacher has got retired. Also how to use If and then in this instant

Thank you.

Wilfred.

8. You need to replace the DOB & Date fields with those in your database. Possibly this:

Service to date = DateDiff("yyyy", [Reg],[Left]) + (Format([Reg], "mmdd") > Format([Left], "mmdd")) _
+ DateDiff("yyyy", [Returned], [Left2]) + (Format([Returned], "mmdd") > Format([Left2], "mmdd")) _
+ DateDiff("yyyy", [Returned2], Date()) + (Format([Returned2], "mmdd") > Format(Date(), "mmdd"))

NOTE: I've just used the field names from your original incorrect expression.
You used Date() in your expression so I have as well. Perhaps that should be RetirementDate instead.

The above doesn't need Int as each part of the expression is an integer

However, what if someone leaves and returns 3 or more times before retiring. Your field structure can't handle that
That's why it needs to be normalised.

Also you may get errors if someone has no [Left] data (another bad choice of field name) or Returned, Left2, Returned2 data
So you may need to tweak it further

-Add these two functions to a standard module
Code:
```Public Function Min2(a As Variant, b As Variant) As Variant
'
' Returns the lesser of 2 values
'
Min2 = IIf(a < b, a, b)
End Function
Public Function Max2(a As Variant, b As Variant) As Variant
'
' Returns the greater of 2 values
'
Max2 = IIf(a > b, a, b)
End Function```
Service to date - =Int(DateDiff("yyyy",[Reg],[Left])+DateDiff("yyyy",[Returned],[Left2])+DateDiff("yyyy",[Returned2],Min2(Date(),[Retired]))/365.25)

Again, I would follow the suggestions offered here, but I think this would address your current question.

Cheers,

10. Sorry Vlad but your suggestion only covers part of the problem.
It won't solve specific examples like the dates I gave in point 1 of my first reply - see post #5

11. I know Colin, that is why I put the qualifier:
, I wanted to show the OP how to deal with his specific question of how to easy get the minimum of two dates (retirement vs Date()).

Cheers,

12. OK thanks - that went over my head

13. Novice
Windows 10 Access 2016
Join Date
Jan 2021
Posts
4

Thank you for assistance. Now the new code added above that is
Min2(Date(),[Retired])), I have a question in my mind.
I am not making any query but inserting codes to a form field.

Where am I to insert these conditions as above?

Public Function Min2(a As Variant, b As Variant) As Variant
'
' Returns the lesser of 2 values
'
Min2 = IIf(a < b, a, b)
End Function
Public Function Max2(a As Variant, b As Variant) As Variant
'
' Returns the greater of 2 values
'
Max2 = IIf(a > b, a, b)
End Function

Thank you.
Wilfred.

14. Hi Wilfred,

You need to add this (and maybe the Max2) function to a standard VBA module. In the Create ribbon click the Module button and paste the two functions.

Cheers,