Update as per excel calculations...

[hrprabhu]

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_lea veType 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

[NeoPa]

Possible outcomes are :

1. "N/A"
2. G3 (StartIncDate) + Q3 (daysInYear)
3. G3 (StartIncDate) + Q3 (daysInYear) + S3 (countLeave)

1. If K3 (newIncrement) is "N/A" then A.
2. ELSE If I3 (ceaseDate) is "CANCELLED" then B.
3. If P3 (dischargeDate) - G3 (StartIncDate) > I3 (ceaseDate) + Q3 (daysInYear) - H3 (commDate)
   OR
   P3 has not been set
   then C.
4. ELSE (which is equivalent to P3 is set and P3 - G3 <= I3 + Q3 - H3) A.

I hope that helps to clarify it for you.

[hrprabhu]

Thanks....Helps a lot.

[NeoPa]

Pleased to hear it :-)

I was impressed with the efforts obviously taken to give as much clarity to the question as possible.

[hrprabhu]

Hi NeoPa,

I have used the following code and it is not working. Could you please tell me what I am doing incorrectly.

Cheers

    If newIncrement = "N/A" Then
        effDate = ""
    ElseIf ceaseDate = "" Then
            effDate = startIncDate + daysInYear
    ElseIf (dischargeDate - startIncDate) > (ceaseDate - commDate + daysInYear) Then
        effDate = startIncDate + daysInYear + countLeave
    ElseIf dischargeDate = "" Then
        effDate = startIncDate + daysInYear + countLeave
    ElseIf dischargeDate <> "" And (dischargeDate - startIncDate) <= (ceaseDate - commDate + daysInYear) Then
        effDate = ""
    End If

[NeoPa]

If you tell me what you mean by "not working" I'll happily look at it for you. Details are not optional in situations such as these ;-)

[hrprabhu]

NeoPa,

finally got it working..

    If newIncrement <> "N/A" Then
        If Nz(ceaseDate, "") = "" Then
            effDate = startIncDate + daysInYear
        Else
            If dischargeDate + 1 - startIncDate > (ceaseDate + 1 - commDate) + daysInYear Or Nz(dischargeDate, "") = "" Then
                effDate = startIncDate + daysInYear + countLeave
            Else
                effDate = ""
            End If
        End If
    Else
        effDate = ""
    End If

Thanks for your help.

[NeoPa]

First: Well done. It's always good if you can work your way through a problem and come up with the correct answer. Good for you.

For the other point please see my PM. It is about your posting habits so is not appropriate to include here.

[hrprabhu]

Thanks for your input...

Cheers
Raghu