Karen Schaefer
asked on
If statement not return True when it should based on the data
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.
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
ASKER
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?
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?
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
However, some of the values of the fields are numbers to exponential.
How would you recommend handling values like : 7.87772796174584E-02
Hi,
It is just a representation, it is 0.07877... or if you want 7.877%
Regards
It is just a representation, it is 0.07877... or if you want 7.877%
Regards
ASKER
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
ASKER
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.
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.
ASKER
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.
Still having issues with the entire code and the order of the loops/if statements - I will post new question.
thanks for your input.
Which data type have nfld & nTier6 if they are strings it could produce this error
you should convert to numbers before testing them
Regards