IIf function not giving the last formula result correctly (1 Viewer)

KANNI

New member
Local time
Tomorrow, 01:25
Joined
Apr 28, 2018
Messages
4
am handling a formula which is throwing correct amounts for the three clauses but not giving the correct result for the last clause..... i am unable to locate where it went wrong!!

=IIf([BASICPEN]<=3550,[BASICPEN]*0.24*[DRS]/100,IIf([BASICPEN]>3550<=5650,(3550*0.24*[DRS]/100)+(([BASICPEN]-3550)*0.2*[DRS]/100),IIf([BASICPEN]>5650<=6010,(3550*0.24*[DRS]/100)+(2100*0.2*[DRS]/100)+(([BASICPEN]-5650)*0.12*[DRS]/100),IIf([BASICPEN]>6010,(3550*0.24*[DRS]/100)+(2100*0.2*[DRS]/100)+(360*0.12*[DRS]/100)+(([BASICPEN]-6010)*0.06*[DRS]/100),0))))

when the BASICPEN is 10000/- and DRS is 32 it should give Rs.497.46
but its giving Rs.685.44 ......
...any clue!!!!
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 12:55
Joined
Aug 30, 2003
Messages
36,118
Your greater than/less than tests are flawed, they would need to repeat the field and use And, like:

Field > 10 And Field < 20

That said, since the testing will stop once a test is met, simply test for less than with increasingly higher values.
 

KANNI

New member
Local time
Tomorrow, 01:25
Joined
Apr 28, 2018
Messages
4
thank u Mr.Paul.. it just solved the issue.....
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 12:55
Joined
Aug 30, 2003
Messages
36,118
Happy to help!
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 03:55
Joined
May 7, 2009
Messages
19,169
why not use a function to simplify.
copy and paste in a module

Code:
Option Compare Database
Option Explicit

Public Function fncBPen(BPen As Variant, drs As Variant) As Double
    
    Dim ans As Double
    
    BPen = Nz(BPen, 0)
    drs = Nz(drs, 0)
    
    If BPen > 6010 Then
        ans = ans + ((BPen - 6010) * 0.06 + drs / 100)
        BPen = BPen - 6010
    End If
    If BPen > 5650 Then
        ans = ans + ((BPen - 5650) * 0.12 * drs / 100)
        BPen = BPen - 5650
    End If
    If BPen > 3550 Then
        ans = ans + ((BPen - 3550) * 0.2 * drs / 100)
        BPen = BPen - 3550
    End If
    If BPen > 0 Then
        ans = ans + (BPen * 0.24 * drs / 100)
    End If
    
    fncBPen = ans
    
End Function

on yur qeuery:

select basicpen, drs, fncBPen([basicpen], [drs]) As Amount from yourTable;

i am sure some smart guy will disagree as expected.
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 12:55
Joined
Aug 30, 2003
Messages
36,118
Thanks, I appreciate the compliment. ;)

For a complicated IIf() like that, a function is certainly a good alternative. Personally I'd write it to be a little more efficient than that though, using ElseIf or Select/Case.
 

Users who are viewing this thread

Top Bottom