Need help with formula returning #Error...

[Deech]

Hello Everyone,

I am new to Access and I am trying to write a formula in the control source of a field to calculate the number of years after a termination date is entered. I got the formula to work when the field is populated but I am having a hard time putting a formula together to take into consideration a blank field when no date is entered. The formula I currently have is below and it returns the values I need when there is a date in the field but an #Error when it is blank.

=IIf(Format([TerminationDate],"yyyy")=9999,"",[TerminationDate]+2191)

What I am trying to do is return a blank when the year placed in the field is 9999, return the date plus 6 years when any other year is entered, and return a blank when the field is blank.

Examples of what I am looking for:
1/1/9999 I want the field to return blank
1/1/2009 I want the field to return 1/1/2015

Your help is greatly appreciated.
Deech

 

[Kiwiman]

Howzit

Welcome to the forum

Try...

  IIf([TerminationDate]=Null,"",IIf(Year([TerminationDate])=2001,"",DateAdd("yyyy",6,[TerminationDate])))

 

[Deech]

Sweet Deal! Thank you Kiwiman it worked like a charm. I guess I was making it harder on myself. Thanks again man, cheers.