If statement not return True when it should based on the data Asked by: kfschaefer1 (1 Viewer)

[kfschaefer1]

I have a complex IF Statement within VBA

when I step thru the code the if statement variables should return true but instead treats it as False. See '>>>> this happens on the 2nd Pass of a Do Loop Statement, the First Loop the if Statement Returns True

Variable Values:

Record 1
contractNo: 00001634
nfld: 33.40%
nTier6: 30.00%


Record 2
contractNo: 00001634
nfld: 137.52%
nTier6: 28.50%

so the 2nd if should be true but it acts as false an moves to next If statement.

If nfld = Format(0, "Percent") Then
                    nOvrAmt = 0
                    BkOvrCalc = nOvrAmt
                               GoTo cont:
  '>>>              ElseIf nfld > nTier6 Then
                    nOvrAmt = rs.Fields("TotalNetUSExp") * rs1.Fields("T6E").Value
                    BkOvrCalc = nOvrAmt
                    GoTo cont:
                
                ElseIf nfld < nTier6 Then
                   For nTr = 1 To 4
                       'determine which Tier value to use
                       nTierNo = "Tier" & nTr
                       strNextField = "Tier" & nTr + 1
                       'Determine the % Payout:
                       strfld = "T" & nTr & "E"
                       strfldNext = "T" & nTr + 1 & "E"
                            nfld1 = Format(rs.Fields(nTierNo).Value, "Percent")
                            nfld2 = Format(rs.Fields(strNextField).Value, "Percent")
                           Debug.Print "nfld1:" & nfld1
                           Debug.Print "nfld2:" & nfld2
                           If nfld > nfld1 Then
                               nOvrAmt = 0
                               BkOvrCalc = nOvrAmt
                               GoTo cont:
                           ElseIf nfld > nfld1 And nfld < nfld2 = True Then
                               nOvrAmt = rs.Fields("TotalNetUSExp") * rs1.Fields(strfld).Value
                               BkOvrCalc = nOvrAmt
                               GoTo cont:
                           End If
                    nTr = nTr + 1
                    Next nTr
                End If

 

[Insane_ai]

I think your issue is with the formatting as a percent. Have you tried using decimals?

Your basic evaluation appears to be:

If nfld = 0 then
'do this
ElseIf nfld > nTier6
'do this instead
ElseIf nfld < nTier6
'figure out what tier it belongs to
End If

Instead of looking for a 0 percent and dealing with a percentage over 100 try working with the decimal equivilents .3340 and 1.3752

The underlying field settings may affect what is actually being returned and causing this as well.