Solved

If statement not return True when it should based on the data

Posted on 2014-03-14
8
524 Views
Last Modified: 2014-03-14
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

Open in new window

0
Comment
Question by:Karen Schaefer
  • 5
  • 3
8 Comments
 
LVL 48

Expert Comment

by:Rgonzo1971
Comment Utility
Hi

Which data type have nfld & nTier6 if they are strings it could produce this error

you should convert to numbers before testing them

Regards
0
 

Author Comment

by:Karen Schaefer
Comment Utility
they are actually a Percent is there a datatype I should use instead of string?

Also note the I am retrieving the actual field Name prior to gettng the actual value.  Does this come in to play with the varialbes note reading the data correctly?

sample Data
Public Function BkOvrCalc(ByVal gContractID As String) As Long
Dim curDB As DAO.Database
Dim strSQL As String, strSQL1 As String
Dim rs As DAO.Recordset, rs1 As DAO.Recordset
Dim strField As String, strNextField As String
Dim strfld As String, strfldNext As String
Dim nTr As Long
Dim nfld As String, nfld1 As String, nfld2 As String
Dim x As Integer, i As Integer
Dim nTierNo As String, nTier6 As String
Dim nOvrAmt As Currency

   On Error GoTo BkOvrCalc_Error

Set curDB = CurrentDb

    'delete current data from tblOverride_ExpectQtrlyTotals
    curDB.Execute ("Delete * from tblOverride_ExpectQtrlyTotals")
    
    'List of all Contracts and Quarters Totals for calculation of Override Dollars by Tier%
    strSQL = "SELECT *" & _
                    " FROM qryOverride_Calc" & _
                    " Where ContractNumber = " & Chr(34) & gContractID & Chr(34) & ""
    Set rs = curDB.OpenRecordset(strSQL)
    
    'List of all Contracts and AccountPercentage, Account Dollars & Payout Percentage
    strSQL1 = "SELECT ContractNumber, ORType, T1E, T2E, T3E, T4E, T5E, T6E," & _
                " AT1Per, AT2Per, AT3Per, AT4Per, AT5Per, AT6Per," & _
                " AT1Dol, AT2Dol, AT3Dol, AT4Dol, AT5Dol, AT6Dol" & _
            " FROM tblContracts" & _
            " WHERE ContractNumber = " & Chr(34) & gContractID & Chr(34) & ""
    Set rs1 = curDB.OpenRecordset(strSQL1)
'OvtAmount: IIf([PctYrlyIncrease]=0,BkOvrCalc([qrySummaryExpectation_Detail].[contractNumber]),IIf([PctYrlyIncrease]>[Tier6],[TotalNetUSExp]*[T6E],0))
    'rs.MoveFirst
    Do Until rs.EOF
        ' Override Code Type
        x = rs.Fields("ORType")
        nfld = Format(rs.Fields("PctYrlyIncrease").Value, "Percent")
        nTier6 = Format(rs.Fields("Tier6").Value, "Percent")
          Debug.Print "contractNo:" & gContractID
          Debug.Print "nfld: " & nfld
          Debug.Print "nTier6:" & nTier6
        Select Case x ' OverRide Type
            Case 1 'Quarters
               If nfld = Format(0, "Percent") Then

Open in new window

0
 
LVL 48

Accepted Solution

by:
Rgonzo1971 earned 500 total points
Comment Utility
You should completely correct your code not formatting your data to percent it wil  only make thing easier to compare ( then instead of [if nfld = Format(0, "Percent") then], you could use [if nfld = 0 then].

To know which data type you should use for the variables nfld and nTier6,  you have to get them from the access tables design.

EDIT

Percents are only a formatting That's why you use the format function to produce them, by doing this you convert a number into a string if you do not convert them at debug.print you will get 0.30 instead of "30%" and that's right because 0.30 is 30% of 1 and all your calculations will be correct



Regards
0
 

Author Comment

by:Karen Schaefer
Comment Utility
I changed the nfld, nfld2, etc to Double and we were on the same page I removed the format from the string value of the nfld, etc.

However, some of the values of the fields are numbers to exponential.

How would you recommend handling values like : 7.87772796174584E-02
0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 48

Expert Comment

by:Rgonzo1971
Comment Utility
Hi,

It is just a representation, it is 0.07877... or if you want 7.877%

Regards
0
 

Author Comment

by:Karen Schaefer
Comment Utility
Ok I am still having issues with Datatype mismatch.  I have played around with various datatypes for the  Percentage(Tier1-6)

Public Function BkOvrCalc(ByVal gContractID As String) As Long
Dim curDB As DAO.Database
Dim strSQL As String, strSQL1 As String
Dim rs As DAO.Recordset, rs1 As DAO.Recordset
Dim strField As String, strNextField As String
Dim strfld As String, strfldNext As String
Dim nfld As Double, nfld1 As Double, nfld2 As Double
Dim nTierNo As Variant, nTier6 As Double
Dim nTr As Integer
Dim x As Integer
Dim nOvrAmt As Currency

   On Error GoTo BkOvrCalc_Error

Set curDB = CurrentDb

    'delete current data from tblOverride_ExpectQtrlyTotals
    'curDB.Execute ("Delete * from tblOverride_ExpectQtrlyTotals")
    
    'List of all Contracts and Quarters Totals for calculation of Override Dollars by Tier%
    strSQL = "SELECT *" & _
                    " FROM qryOverride_Calc" & _
                    " Where ContractNumber = " & Chr(34) & gContractID & Chr(34) & " and quarter = 1"
    Set rs = curDB.OpenRecordset(strSQL)
    
    'List of all Contracts and AccountPercentage, Account Dollars & Payout Percentage
    strSQL1 = "SELECT ContractNumber, ORType, T1E, T2E, T3E, T4E, T5E, T6E," & _
                " AT1Per, AT2Per, AT3Per, AT4Per, AT5Per, AT6Per," & _
                " AT1Dol, AT2Dol, AT3Dol, AT4Dol, AT5Dol, AT6Dol" & _
            " FROM tblContracts" & _
            " WHERE ContractNumber = " & Chr(34) & gContractID & Chr(34) & ""
    Set rs1 = curDB.OpenRecordset(strSQL1)
    
    rs.MoveFirst
    Do Until rs.EOF
        ' Override Code Type
        x = rs.Fields("ORType")
        nfld = rs.Fields("PctYrlyIncrease").Value
        nTier6 = rs.Fields("Tier6").Value
          Debug.Print "contractNo:" & gContractID
          Debug.Print "nfld: " & nfld
          Debug.Print "nTier6:" & nTier6
        Select Case x ' OverRide Type
            Case 1 'Quarters
               If nfld = Format(0, "Percent") Then
                    nOvrAmt = 0
                    BkOvrCalc = nOvrAmt
                    GoTo cont:
                End If
                If nfld > nTier6 Then
                    nOvrAmt = rs.Fields("TotalNetUSExp") * rs1.Fields("T6E").Value
                    BkOvrCalc = nOvrAmt
                    GoTo cont:
                End If
                If 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 = rs.Fields(nTierNo).Value
                            nfld2 = rs.Fields(strNextField).Value
                           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
            Case 2 'Annual Flat%
            Case 3 'Annual Flat$
        End Select
cont:

        rs.MoveNext
    Loop

'onExit:
'Set rs = Nothing
'    Set rs1 = Nothing
'    rs.Close
'    rs1.Close
'
   On Error GoTo 0
   Exit Function

BkOvrCalc_Error:

   MsgBox "Error " & Err.Number & " (" & Err.Description & _
        ") in procedure BkOvrCalc of Module basUtilities"

End Function

Open in new window

0
 

Author Comment

by:Karen Schaefer
Comment Utility
I figured out the typemismatch  on closing I have the

set rs = nothing before the rs.close, etc.

However I still not getting the IF statements to return the correct true or false

It gets stuck in a loop and then crashes the mdb.

Ok I really need some help with the entire looping of the proper records.
0
 

Author Closing Comment

by:Karen Schaefer
Comment Utility
Atleast I got the Field type working - no more mismatch issues.

Still having issues with the entire code and the order of the loops/if statements - I will post new question.

thanks for your input.
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

CCModeler offers a way to enter basic information like entities, attributes and relationships and export them as yEd or erviz diagram. It also can import existing Access or SQL Server tables with relationships.
Shadow IT is coming out of the shadows as more businesses are choosing cloud-based applications. It is now a multi-cloud world for most organizations. Simultaneously, most businesses have yet to consolidate with one cloud provider or define an offic…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now