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

[isladogs]

I did stress that you would have problems with this code back in post #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

The problem is that you are trying to use an Excel like data structure and Access is not designed to work like a spreadsheet

You really need to learn about normalising data in Access or do this in Excel

You can get a little further by wrapping each date field in the Nz function but it will rapidly get very messy
If you must persist with this data structure, I would suggest you create a user defined function for the purpose.

Also remember that the expression as supplied will not give any part years served.

[ssanfu]

From Post #1

....Teachers are allowed to leave the school for taking of examinations, wedding etc. Left and Left2 are for leaving dates.......

You should be aware that "Left" is a reserved word and a built in function in Access and shouldn't be used for object names.

I agree with Colin and think you should write a UDF because of the complexity of the calculation.