R
RDB via AccessMonster.com
I recently inherited a database with one of our reports being a contract.
The contract is suppose to list a max. of 3 payment options depending on the
# of payment dates entered and at this point it will list the first two
payment options but not the third. The first option is the total sum, the
second option is the total sum divide in two and adding a $25 admin fee to
each, and the third option is dividing the total in three and also adding $25
to each. Also when it divides the sum it will round to the nearest penny on
both amounts but should only round up on one to make the sum accurate. Ex.
On a two payment option contract total $333.33 should be $166.66 + $25 admin
fee and $166.67 + $25 admin fee. I am new at this and am having trouble
correcting the vb of the last person working on this database to make it work
correctly. If someone is able to help I would be very greatful. Here is the
current vb portion of this database relevant to the contract payments:
' Dummy Dates
' gInstallmentFirstDate = #7/11/01#
' gInstallmentSecondDate = #7/12/01#
' gInstallmentThirdDate = #7/13/01#
' Section 8 subsection A
txtDollarsDue = Format(ConditionSum, "Currency")
txtDollarsDueDate_A = gInstallmentFirstDate
' Dim IsOne As Boolean PJ
Dim IsThree As Boolean
' Dim IsTwo As Boolean pj 1/7
Dim FirstDue As Currency
Dim SecondDue As Currency
Dim ThirdDue As Currency
If (ConditionSum <= 1200) Then 'Or (IsEmpty(gInstallmentSecondDate))) Then
'PJ
txtDollarsDue = Format(ConditionSum, "Currency")
' txtDollarsDue = Format(ConditionSum - (2 * ApplFee), "Currency")
' txtDollarsDue = Format(ConditionSum - ApplFee, "Currency")
txtDollarsDueDate_A = gInstallmentFirstDate 'PJ
End If
' PJ
************************************************************************
4/20/05
' If only one payment date is entered, don't offer installment payments
' no matter what the dollar amount on the contract is
' If two payment dates are entered calculate for two installment payments
' If (ConditionSum > 1200 And ConditionSum <= 5000 And Not IsEmpty
(gInstallmentSecondDate)) Then
If (ConditionSum > 1200 And ConditionSum <= 5000) Then
If (Not IsEmpty(gInstallmentSecondDate)) And (gInstallmentSecondDate <>
"12:00:00 AM") Then
' If (Not IsEmpty(gInstallmentSecondDate)) Then '**to bypass
installment payments PJ
' txtTwoDateSecond = gInstallmentSecondDate
Call CalculateSplit(ConditionSum, True, False, FirstDue, SecondDue,
ThirdDue)
txtDollarsDue = Format(ConditionSum, "Currency")
' txtDollarsDue = Format(ConditionSum - (2 * ApplFee), "Currency")
'PJ 5/25
txtDollarsDueDate_A = gInstallmentFirstDate
txtTwoDueFirst = Format(txtDollarsDue / 2 + 25, "Currency")
txtTwoDueSecond = Format(txtDollarsDue / 2 + 25, "Currency")
txtTwoDateFirst = gInstallmentFirstDate
txtTwoDateSecond = gInstallmentSecondDate
Else
txtTwoDueFirst = vbNullString
txtTwoDateFirst = vbNullString
txtTwoDueSecond = vbNullString
txtTwoDateSecond = vbNullString
End If
' If (Not IsEmpty(gInstallmentSecondDate)) And (gInstallmentSecondDate <> "12:
00:00 AM") Then
' txtTwoDateSecond = gInstallmentSecondDate
' Else
' txtTwoDueFirst = vbNullString
' txtTwoDateFirst = vbNullString
' txtTwoDueSecond = vbNullString
' txtTwoDateSecond = vbNullString
End If
' PJ for amounts greater than $5000, If only one date entered, don't offer
' installment payment options no matter what the dollar amount on the
contract is
' For two payment dates, offer two payment schedule, and if three dates
entered calculate
' split for three payments
If (ConditionSum > 5000) Then
If (Not IsEmpty(gInstallmentThirdDate)) And (gInstallmentThirdDate <> "12:00:
00 AM") Then
' If (Not IsEmpty(gInstallmentSecondDate)) And (gInstallmentSecondDate <>
"12:00:00 AM") Then
' txtTwoDateSecond = gInstallmentSecondDate
Call CalculateSplit(ConditionSum, True, True, FirstDue, SecondDue,
ThirdDue)
txtTwoDueFirst = Format(txtDollarsDue / 2 + 25, "Currency")
'4/20
txtTwoDueSecond = Format(txtDollarsDue / 2 + 25, "Currency")
'4/20
txtTwoDateFirst = gInstallmentFirstDate
txtTwoDateSecond = gInstallmentSecondDate
txtDollarsDue = Format(ConditionSum, "Currency")
' txtDollarsDue = Format(ConditionSum - (2 * ApplFee), "Currency")
'PJ 5/18
txtDollarsDueDate_A = gInstallmentFirstDate 'PJ
txtThreeDueFirst = Format(txtDollarsDue / 3 + 25, "Currency")
txtThreeDueSecond = Format(txtDollarsDue / 3 + 25, "Currency")
txtThreeDueThird = Format(txtDollarsDue / 3 + 25, "Currency")
txtThreeDateFirst = gInstallmentFirstDate
txtThreeDateSecond = gInstallmentSecondDate
txtThreeDateThird = gInstallmentThirdDate
lblThree.Properties("Visible") = True
lblThreeBefore.Properties("Visible") = True
lblThreeBeforeSecond.Properties("Visible") = True
lblThreeBeforeThird.Properties("Visible") = True
txtThreeDueFirst.Properties("Visible") = True
txtThreeDateFirst.Properties("Visible") = True
txtThreeDueSecond.Properties("Visible") = True
txtThreeDateSecond.Properties("Visible") = True
txtThreeDueThird.Properties("Visible") = True
txtThreeDateThird.Properties("Visible") = True
Line1.Properties("Visible") = True
Line2.Properties("Visible") = True
Line3.Properties("Visible") = True
Line4.Properties("Visible") = True
Line5.Properties("Visible") = True
Line6.Properties("Visible") = True
Else
If (Not IsEmpty(gInstallmentSecondDate)) And (gInstallmentSecondDate <>
"12:00:00 AM") Then
' If (IsEmpty(gInstallmentThirdDate)) Then
' Call CalculateSplit(ConditionSum, True, False, FirstDue, SecondDue,
ThirdDue)
Call CalculateSplit(ConditionSum, True, False, FirstDue, SecondDue,
ThirdDue)
txtDollarsDue = Format(ConditionSum - (2 * ApplFee), "Currency") 'PJ
4/19
txtDollarsDueDate_A = gInstallmentFirstDate
txtTwoDueFirst = Format(txtDollarsDue / 2 + 25, "Currency")
txtTwoDueSecond = Format(txtDollarsDue / 2 + 25, "Currency")
txtTwoDateFirst = gInstallmentFirstDate
txtTwoDateSecond = gInstallmentSecondDate
' txtDollarsDue = Format(ConditionSum - ApplFee, "Currency") 'PJ
' txtDollarsDueDate_A = gInstallmentFirstDate 'PJ
' txtTwoDueFirst = Format(txtDollarsDue / 2 + 25, "Currency")
'4/20
' txtTwoDueSecond = Format(txtDollarsDue / 2 + 25, "Currency")
'4/20
' txtTwoDateFirst = gInstallmentFirstDate
' txtTwoDateSecond = gInstallmentSecondDate
' txtTwoDateFirst = gInstallmentFirstDate
' txtTwoDateSecond = gInstallmentSecondDate
' txtTwoDueFirst = vbNullString
' txtTwoDateFirst = vbNullString
' txtTwoDueSecond = vbNullString
' txtTwoDateSecond = vbNullString
' If (ConditionSum > 5000) Then
' If (Not IsEmpty(gInstallmentSecondDate)) And (gInstallmentSecondDate <> "12:
00:00 AM") And
' If (Not IsEmpty(gInstallmentThirdDate)) And (gInstallmentThirdDate <>
"12:00:00 AM") Then
' txtTwoDateSecond = gInstallmentSecondDate
' txtThreeDateThird = gInstallmentThirdDate
' Call CalculateSplit(ConditionSum, True, True, FirstDue, SecondDue,
ThirdDue)
' Else
' txtThreeDueFirst = vbNullString
' txtThreeDateFirst = vbNullString
' txtThreeDueSecond = vbNullString
' txtThreeDateSecond = vbNullString
' txtThreeDueThird = vbNullString
' txtThreeDateThird = vbNullString
' txtTwoDueFirst = vbNullString 'pj 1/28
' txtTwoDateFirst = vbNullString 'pj 1/28
' txtTwoDueSecond = vbNullString 'pj 1/28
' txtTwoDateSecond = vbNullString 'pj 1/28
' txtThreeDueFirst = vbNullString
' txtThreeDateFirst = vbNullString
' txtThreeDueSecond = vbNullString
' txtThreeDateSecond = vbNullString
' txtThreeDueThird = vbNullString
' txtThreeDateThird = vbNullString
' lblThree.Properties("Visible") = False
' lblThreeBefore.Properties("Visible") = False
' lblThreeBeforeSecond.Properties("Visible") = False
' lblThreeBeforeThird.Properties("Visible") = False
' txtThreeDueFirst.Properties("Visible") = False
' txtThreeDateFirst.Properties("Visible") = False
' txtThreeDueSecond.Properties("Visible") = False
' txtThreeDateSecond.Properties("Visible") = False
' txtThreeDueThird.Properties("Visible") = False
' txtThreeDateThird.Properties("Visible") = False
' Line1.Properties("Visible") = False
' Line2.Properties("Visible") = False
' Line3.Properties("Visible") = False
' Line4.Properties("Visible") = False
' Line5.Properties("Visible") = False
' Line6.Properties("Visible") = False
End If
End If
End If
' PJ
' blank out three payment schedule fields if third payment date not
entered
' If (Not IsEmpty(gInstallmentThirdDate)) And (gInstallmentThirdDate
<> "12:00:00 AM") Then
' MsgBox "No Records Found Matching Specified Criteria." + Chr(13) +
_ *** 4/29
' The following blank contract form can be filled in by hand."
***4/29
' End If
End If
End Sub
' PJ need to work in here to get rid of second payment stuff showing up
' SET UP IsTwo then send istwo true, isthree false (for 2 pmnt), or both
false (for one pmnt)
' or both true for three
Private Sub CalculateSplit(Sum As Currency, IsTwo As Boolean, IsThree As
Boolean, _
FirstDue As Currency, SecondDue As Currency, ThirdDue As Currency)
' IsTwo routine? look at CalculateSplit 1/6
If (Not IsTwo) And (Not IsThree) Then ' 1/28
FirstDue = Sum ' 1/28
FirstDue = Format(FirstDue, "Currency") ' 1/27
Else
' 1/27
If (IsTwo) And (Not IsThree) Then
FirstDue = Sum / 2 - 25
FirstDue = Format(FirstDue + 25, "Currency")
SecondDue = Format(FirstDue + 25, "Currency")
Else
' PJ 1/7
If (IsTwo) And (IsThree) Then
FirstDue = Sum / 2
FirstDue = Format(FirstDue + 25, "Currency")
SecondDue = Sum - FirstDue + 25
FirstDue = Sum / 3
FirstDue = Format(FirstDue + 25, "Currency")
SecondDue = Format(FirstDue + 25, "Currency")
ThirdDue = Format(Sum - FirstDue - SecondDue, "Currency") + 25
End If
End If
' Else
' IsThree = True
End If
End Sub
The contract is suppose to list a max. of 3 payment options depending on the
# of payment dates entered and at this point it will list the first two
payment options but not the third. The first option is the total sum, the
second option is the total sum divide in two and adding a $25 admin fee to
each, and the third option is dividing the total in three and also adding $25
to each. Also when it divides the sum it will round to the nearest penny on
both amounts but should only round up on one to make the sum accurate. Ex.
On a two payment option contract total $333.33 should be $166.66 + $25 admin
fee and $166.67 + $25 admin fee. I am new at this and am having trouble
correcting the vb of the last person working on this database to make it work
correctly. If someone is able to help I would be very greatful. Here is the
current vb portion of this database relevant to the contract payments:
' Dummy Dates
' gInstallmentFirstDate = #7/11/01#
' gInstallmentSecondDate = #7/12/01#
' gInstallmentThirdDate = #7/13/01#
' Section 8 subsection A
txtDollarsDue = Format(ConditionSum, "Currency")
txtDollarsDueDate_A = gInstallmentFirstDate
' Dim IsOne As Boolean PJ
Dim IsThree As Boolean
' Dim IsTwo As Boolean pj 1/7
Dim FirstDue As Currency
Dim SecondDue As Currency
Dim ThirdDue As Currency
If (ConditionSum <= 1200) Then 'Or (IsEmpty(gInstallmentSecondDate))) Then
'PJ
txtDollarsDue = Format(ConditionSum, "Currency")
' txtDollarsDue = Format(ConditionSum - (2 * ApplFee), "Currency")
' txtDollarsDue = Format(ConditionSum - ApplFee, "Currency")
txtDollarsDueDate_A = gInstallmentFirstDate 'PJ
End If
' PJ
************************************************************************
4/20/05
' If only one payment date is entered, don't offer installment payments
' no matter what the dollar amount on the contract is
' If two payment dates are entered calculate for two installment payments
' If (ConditionSum > 1200 And ConditionSum <= 5000 And Not IsEmpty
(gInstallmentSecondDate)) Then
If (ConditionSum > 1200 And ConditionSum <= 5000) Then
If (Not IsEmpty(gInstallmentSecondDate)) And (gInstallmentSecondDate <>
"12:00:00 AM") Then
' If (Not IsEmpty(gInstallmentSecondDate)) Then '**to bypass
installment payments PJ
' txtTwoDateSecond = gInstallmentSecondDate
Call CalculateSplit(ConditionSum, True, False, FirstDue, SecondDue,
ThirdDue)
txtDollarsDue = Format(ConditionSum, "Currency")
' txtDollarsDue = Format(ConditionSum - (2 * ApplFee), "Currency")
'PJ 5/25
txtDollarsDueDate_A = gInstallmentFirstDate
txtTwoDueFirst = Format(txtDollarsDue / 2 + 25, "Currency")
txtTwoDueSecond = Format(txtDollarsDue / 2 + 25, "Currency")
txtTwoDateFirst = gInstallmentFirstDate
txtTwoDateSecond = gInstallmentSecondDate
Else
txtTwoDueFirst = vbNullString
txtTwoDateFirst = vbNullString
txtTwoDueSecond = vbNullString
txtTwoDateSecond = vbNullString
End If
' If (Not IsEmpty(gInstallmentSecondDate)) And (gInstallmentSecondDate <> "12:
00:00 AM") Then
' txtTwoDateSecond = gInstallmentSecondDate
' Else
' txtTwoDueFirst = vbNullString
' txtTwoDateFirst = vbNullString
' txtTwoDueSecond = vbNullString
' txtTwoDateSecond = vbNullString
End If
' PJ for amounts greater than $5000, If only one date entered, don't offer
' installment payment options no matter what the dollar amount on the
contract is
' For two payment dates, offer two payment schedule, and if three dates
entered calculate
' split for three payments
If (ConditionSum > 5000) Then
If (Not IsEmpty(gInstallmentThirdDate)) And (gInstallmentThirdDate <> "12:00:
00 AM") Then
' If (Not IsEmpty(gInstallmentSecondDate)) And (gInstallmentSecondDate <>
"12:00:00 AM") Then
' txtTwoDateSecond = gInstallmentSecondDate
Call CalculateSplit(ConditionSum, True, True, FirstDue, SecondDue,
ThirdDue)
txtTwoDueFirst = Format(txtDollarsDue / 2 + 25, "Currency")
'4/20
txtTwoDueSecond = Format(txtDollarsDue / 2 + 25, "Currency")
'4/20
txtTwoDateFirst = gInstallmentFirstDate
txtTwoDateSecond = gInstallmentSecondDate
txtDollarsDue = Format(ConditionSum, "Currency")
' txtDollarsDue = Format(ConditionSum - (2 * ApplFee), "Currency")
'PJ 5/18
txtDollarsDueDate_A = gInstallmentFirstDate 'PJ
txtThreeDueFirst = Format(txtDollarsDue / 3 + 25, "Currency")
txtThreeDueSecond = Format(txtDollarsDue / 3 + 25, "Currency")
txtThreeDueThird = Format(txtDollarsDue / 3 + 25, "Currency")
txtThreeDateFirst = gInstallmentFirstDate
txtThreeDateSecond = gInstallmentSecondDate
txtThreeDateThird = gInstallmentThirdDate
lblThree.Properties("Visible") = True
lblThreeBefore.Properties("Visible") = True
lblThreeBeforeSecond.Properties("Visible") = True
lblThreeBeforeThird.Properties("Visible") = True
txtThreeDueFirst.Properties("Visible") = True
txtThreeDateFirst.Properties("Visible") = True
txtThreeDueSecond.Properties("Visible") = True
txtThreeDateSecond.Properties("Visible") = True
txtThreeDueThird.Properties("Visible") = True
txtThreeDateThird.Properties("Visible") = True
Line1.Properties("Visible") = True
Line2.Properties("Visible") = True
Line3.Properties("Visible") = True
Line4.Properties("Visible") = True
Line5.Properties("Visible") = True
Line6.Properties("Visible") = True
Else
If (Not IsEmpty(gInstallmentSecondDate)) And (gInstallmentSecondDate <>
"12:00:00 AM") Then
' If (IsEmpty(gInstallmentThirdDate)) Then
' Call CalculateSplit(ConditionSum, True, False, FirstDue, SecondDue,
ThirdDue)
Call CalculateSplit(ConditionSum, True, False, FirstDue, SecondDue,
ThirdDue)
txtDollarsDue = Format(ConditionSum - (2 * ApplFee), "Currency") 'PJ
4/19
txtDollarsDueDate_A = gInstallmentFirstDate
txtTwoDueFirst = Format(txtDollarsDue / 2 + 25, "Currency")
txtTwoDueSecond = Format(txtDollarsDue / 2 + 25, "Currency")
txtTwoDateFirst = gInstallmentFirstDate
txtTwoDateSecond = gInstallmentSecondDate
' txtDollarsDue = Format(ConditionSum - ApplFee, "Currency") 'PJ
' txtDollarsDueDate_A = gInstallmentFirstDate 'PJ
' txtTwoDueFirst = Format(txtDollarsDue / 2 + 25, "Currency")
'4/20
' txtTwoDueSecond = Format(txtDollarsDue / 2 + 25, "Currency")
'4/20
' txtTwoDateFirst = gInstallmentFirstDate
' txtTwoDateSecond = gInstallmentSecondDate
' txtTwoDateFirst = gInstallmentFirstDate
' txtTwoDateSecond = gInstallmentSecondDate
' txtTwoDueFirst = vbNullString
' txtTwoDateFirst = vbNullString
' txtTwoDueSecond = vbNullString
' txtTwoDateSecond = vbNullString
' If (ConditionSum > 5000) Then
' If (Not IsEmpty(gInstallmentSecondDate)) And (gInstallmentSecondDate <> "12:
00:00 AM") And
' If (Not IsEmpty(gInstallmentThirdDate)) And (gInstallmentThirdDate <>
"12:00:00 AM") Then
' txtTwoDateSecond = gInstallmentSecondDate
' txtThreeDateThird = gInstallmentThirdDate
' Call CalculateSplit(ConditionSum, True, True, FirstDue, SecondDue,
ThirdDue)
' Else
' txtThreeDueFirst = vbNullString
' txtThreeDateFirst = vbNullString
' txtThreeDueSecond = vbNullString
' txtThreeDateSecond = vbNullString
' txtThreeDueThird = vbNullString
' txtThreeDateThird = vbNullString
' txtTwoDueFirst = vbNullString 'pj 1/28
' txtTwoDateFirst = vbNullString 'pj 1/28
' txtTwoDueSecond = vbNullString 'pj 1/28
' txtTwoDateSecond = vbNullString 'pj 1/28
' txtThreeDueFirst = vbNullString
' txtThreeDateFirst = vbNullString
' txtThreeDueSecond = vbNullString
' txtThreeDateSecond = vbNullString
' txtThreeDueThird = vbNullString
' txtThreeDateThird = vbNullString
' lblThree.Properties("Visible") = False
' lblThreeBefore.Properties("Visible") = False
' lblThreeBeforeSecond.Properties("Visible") = False
' lblThreeBeforeThird.Properties("Visible") = False
' txtThreeDueFirst.Properties("Visible") = False
' txtThreeDateFirst.Properties("Visible") = False
' txtThreeDueSecond.Properties("Visible") = False
' txtThreeDateSecond.Properties("Visible") = False
' txtThreeDueThird.Properties("Visible") = False
' txtThreeDateThird.Properties("Visible") = False
' Line1.Properties("Visible") = False
' Line2.Properties("Visible") = False
' Line3.Properties("Visible") = False
' Line4.Properties("Visible") = False
' Line5.Properties("Visible") = False
' Line6.Properties("Visible") = False
End If
End If
End If
' PJ
' blank out three payment schedule fields if third payment date not
entered
' If (Not IsEmpty(gInstallmentThirdDate)) And (gInstallmentThirdDate
<> "12:00:00 AM") Then
' MsgBox "No Records Found Matching Specified Criteria." + Chr(13) +
_ *** 4/29
' The following blank contract form can be filled in by hand."
***4/29
' End If
End If
End Sub
' PJ need to work in here to get rid of second payment stuff showing up
' SET UP IsTwo then send istwo true, isthree false (for 2 pmnt), or both
false (for one pmnt)
' or both true for three
Private Sub CalculateSplit(Sum As Currency, IsTwo As Boolean, IsThree As
Boolean, _
FirstDue As Currency, SecondDue As Currency, ThirdDue As Currency)
' IsTwo routine? look at CalculateSplit 1/6
If (Not IsTwo) And (Not IsThree) Then ' 1/28
FirstDue = Sum ' 1/28
FirstDue = Format(FirstDue, "Currency") ' 1/27
Else
' 1/27
If (IsTwo) And (Not IsThree) Then
FirstDue = Sum / 2 - 25
FirstDue = Format(FirstDue + 25, "Currency")
SecondDue = Format(FirstDue + 25, "Currency")
Else
' PJ 1/7
If (IsTwo) And (IsThree) Then
FirstDue = Sum / 2
FirstDue = Format(FirstDue + 25, "Currency")
SecondDue = Sum - FirstDue + 25
FirstDue = Sum / 3
FirstDue = Format(FirstDue + 25, "Currency")
SecondDue = Format(FirstDue + 25, "Currency")
ThirdDue = Format(Sum - FirstDue - SecondDue, "Currency") + 25
End If
End If
' Else
' IsThree = True
End If
End Sub