Update as per the excel calculations...

[raghuprabhu]

I inherited a excel spreadsheet and cannot figure out the calculation in column L of main worksheet.

Function code in Column L

=IF(K3<>"N/A",IF(I3 = "CANCELLED", G3 +Q3, IF(OR(P3+1-G3>(I3+1-H3+Q3,P3=""),G3+Q3+S3,"N/A")),"N/A")

Explaination of the function is as below.

If K3 (newIncrement) is not N/A then

If I3 (ceaseDate) is CANCELLED then use the StartIncDate in G3 plus the number of days in Q (daysInYear - which in this case is 17-Jan-04 plus 366 days)

Else

If P3 (dischargeDate) plus 1 day minus G3 (StartIncDate) is greater than I3 (ceaseDate) plus 1 day minus H3 (commDate) plus the number of days in Q (daysInYear which in this case is plus 366 days)

......OR if P3 (dischargeDate) is blank

then

G3 (StartIncDate) plus the number of days in Q (daysInYear -which in this case is plus 366 days) + plus the number of days in S (countLeave - which in this case is Zero days)

Else N/A

The last NA simply means that the first line “If K3 (newIncrement) is not N/A” was in fact NA so don’t do anything of the calculations in the middle.

I want to replicate this in access. I want this in the Afterupdate_leaveType and the out put is effDate.

The "Or" is confusing me.

Attached are both the spreadsheet and a small database.

In Access the relative columns are.

G3 = StartIncDate
H3 = commDate
I3 = ceaseDate
K3 = newIncrement
L3 = effDate '<<<<< Want to calculate this
O3 = leaveType
P3 = dischargeDate
Q3 = daysInYear
S3 = countLeave

Thanks

Raghu Prabhu