IF logical based on multiple conditions

[mba_110]

Hi everyone,

I am trying to execute some calculation for employees in query with IF but unable to reach the target.

My expression in query

 IIf([EOSB_Entitle]="Yes" And [ContractStatus]="Resigned",IIf([TotalDays]<=729,IIf([TotalDays]<=1824,[AccrualDays]/3,IIf([TotalDays]<=1825,[AccrualDays]/3*2,IIf([TotalDays]>3650,[AccrualDays])))),0)

IF
[EOSB_Entitle] equal to "Yes" and [ContractStatus]="Resigned"

than

Total days is less than 730 the result should show 0
Total days is greater than 730 but less than 1825 then result should be [Accrualdays]/3
Total days is greater than 1825 but less than 3650 then result should be [Accrualdays]/3*2
Total days is greater than 3650 then [Accrualdays]

otherwise

0

Any help here with above is much appreciated.

 

[RCBricker]

I am not partial to Iif I think it is harder to read as code when trying to change or fix issues.

that said...will this work?

    If [EOSB_Entitle] = "Yes" And [ContractStatus] = "Resigned" Then
        If [TotalDays] <= 729 Then
            lngval = 0
        ElseIf [TotalDays] >= 730 And [TotalDays] <= 1824 Then
            lngval = Sum([Accuraldays] / 3)
        ElseIf [TotalDays] >= 1825 And [TotalDays] <= 3650 Then
            lngval = Sum([Accuraldays] / 3) * 2
        ElseIf [TotalDays] > 3650 Then
            lngval = [AccrualDays]
        End If
    Else
        lngval = 0
    End If

Here is the IIF formula, I think you were just missing a sum.

IIf [EOSB_Entitle] = "Yes" And [ContractStatus] = "Resigned", _
    IIf([Totaldays] <= 729, 0, _
    IIf([Totaldays] >= 730 And [Totaldays] <= 1824, [Accuraldays] / 3, _
    IIf([Totaldays] >= 1825 And [Totaldays] <= 3650, Sum([Accuraldays] / 3) * 2, _
    IIf([Totaldays] > 3650, [Accrualdays], 0))))

 

[mba_110]

IIF formula is not working and i dont know where i can enter your code in query ?

 

[RCBricker]

ahh you are trying to build this into a query. Hmmm not sure how to fix this.

 

[xenou]

It will work in a query. But it should be all on one line. Remove the _ (underscore) characters from the formula, and any unneeded spaces. If it is meant to be used a a column in the query it also needs a column alias/name.

 

[xenou]

Oops, is missing a set of parentheses:

IIf [EOSB_Entitle] = "Yes" And [ContractStatus] = "Resigned", _
    IIf([Totaldays] <= 729, 0, _
    IIf([Totaldays] >= 730 And [Totaldays] <= 1824, [Accuraldays] / 3, _
    IIf([Totaldays] >= 1825 And [Totaldays] <= 3650, Sum([Accuraldays] / 3) * 2, _
    IIf([Totaldays] > 3650, [Accrualdays], 0))))

Should be:

IIf ([EOSB_Entitle] = "Yes" And [ContractStatus] = "Resigned", _
    IIf([Totaldays] <= 729, 0, _
    IIf([Totaldays] >= 730 And [Totaldays] <= 1824, [Accuraldays] / 3, _
    IIf([Totaldays] >= 1825 And [Totaldays] <= 3650, Sum([Accuraldays] / 3) * 2, _
    IIf([Totaldays] > 3650, [Accrualdays], 0)))))

One liner:

IIf([EOSB_Entitle] = "Yes" And [ContractStatus] = "Resigned", IIf([Totaldays] <= 729, 0, IIf([Totaldays] >= 730 And [Totaldays] <= 1824, [Accuraldays] / 3, IIf([Totaldays] >= 1825 And [Totaldays] <= 3650, Sum([Accuraldays] / 3) * 2, IIf([Totaldays] > 3650, [Accrualdays], 0)))))

 

[xenou]

I don't actually see why you have a sum() in the formula at all. If it's not working post some sample data.

 

[xenou]

Might also need some further logic ... for instance, if Totaldays is 731 and Accuraldays is 1825 then I'm not sure you would get any result here.