how to add date if

[ghost]

Hi,

I have a table for employee’s promotions and there is a form that shows the
next promotions based on position and one field for last promotion date. For
example, if the job title is group leader, then add to the last promotion
date 2 years. I have used the following formula and it works only with 3
conditions only
IIf([Grade]="Manager",DateAdd("yyyy",4,[GradeDate]),IIf([Grade]="Sr.Suprvisor",DateAdd("yyyy",3,[GradeDate]),IIf([Grade]="Supervisor",DateAdd("yyyy",5,[GradeDate]),"12/31/9999")))

I have up to 9 position , is there any way to simplify the conditions

 

[John Spencer]

The best way would be to have a table. With the various grades and the
number of years. Then you could use that table in a query along with
your current items to join Grade in your current table to grade in the
Grades table and select the promotion factor from that. Then your
expression might be

IIF([PromotionFactor] is null,#12/31/9999#,
DateAdd("yyyy",[PromotionFactor],[GradeDate]))

The next option would be to write a custom VBA function that you would
pass the Grade and the GradeDate to and have it do the calculation and
return the appropriate result.

The final option would be to extend the nested IIF's (but you may run
into a limit on that one since I think you are limited to seven levels.
Your expression would follow this pattern

IIf([Grade]="Manager",DateAdd("yyyy",4,[GradeDate]),
IIf([Grade]="Sr.Suprvisor",DateAdd("yyyy",3,[GradeDate]),
IIf([Grade]="Supervisor",DateAdd("yyyy",5,[GradeDate]),
IIf(Grade="Flunky",DateAdd("yyyy",7,GradeDate),#12/31/9999#))))

Or you could use the VBA switch function

DateAdd("yyyy",
SWITCH(Grade="Manager",4, Grade="Sr. Supervisor",3,
Grade="Supervisor",5, Grade="Flunky",7, Grade="Gofer",8,
True,9999-Year(GradeDate))
,GradeDate)

The table solution is the most flexible and handles changes to the data
the easiest. All the other solutions will require maintenance if you
ever add a grade, change the name of a grade, or change the promotion
factor for a grade.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================