Change Order of code

[Bob Vance]

This code is taking totals away in the wrong order tbCurrent should be last
to be subtracted, this code is making it the first to be subtracted....Any
help thanks Bob

Function Dues(tb3Months0 As Double, tb2Months0 As Currency, tb1Month0 As
Currency, tbCurrent0 As Currency, months As Integer) As Currency
Dim tb3Months As Currency, tb2Months As Currency, tb1Month As Currency,
tbCurrent As Currency
Dim diff As Double

diff = 0
tb3Months = tb3Months0
tb2Months = tb2Months0
tb1Month = tb1Month0
tbCurrent = tbCurrent0

If tb3Months > 0 Then
If tb2Months < 0 Then
diff = tb3Months + tb2Months
tb3Months = IIf(diff < 0, 0, diff)
tb2Months = IIf(diff < 0, diff, 0)
End If
ElseIf tb3Months < 0 Then
If tb2Months > 0 Then
diff = tb3Months + tb2Months
tb2Months = IIf(diff < 0, 0, diff)
tb3Months = IIf(diff < 0, diff, 0)
End If
End If

If tb3Months > 0 Then
If tb1Month < 0 Then
diff = tb3Months + tb1Month
tb3Months = IIf(diff < 0, 0, diff)
tb1Month = IIf(diff < 0, diff, 0)
End If
ElseIf tb3Months < 0 Then
If tb1Month > 0 Then
diff = tb3Months + tb1Month
tb1Month = IIf(diff < 0, 0, diff)
tb3Months = IIf(diff < 0, diff, 0)
End If
End If

If tb3Months > 0 Then
If tbCurrent < 0 Then
diff = tb3Months + tbCurrent
tb3Months = IIf(diff < 0, 0, diff)
tbCurrent = IIf(diff < 0, diff, 0)
End If
ElseIf tb3Months < 0 Then
If tbCurrent > 0 Then
diff = tb3Months + tbCurrent
tbCurrent = IIf(diff < 0, 0, diff)
tb3Months = IIf(diff < 0, diff, 0)
End If
End If

If tb2Months > 0 Then
If tb1Month < 0 Then
diff = tb2Months + tb1Month
tb2Months = IIf(diff < 0, 0, diff)
tb1Month = IIf(diff < 0, diff, 0)
End If
ElseIf tb2Months < 0 Then
If tb1Month > 0 Then
diff = tb2Months + tb1Month
tb1Month = IIf(diff < 0, 0, diff)
tb2Months = IIf(diff < 0, diff, 0)
End If
End If

If tb2Months > 0 Then
If tbCurrent < 0 Then
diff = tb2Months + tbCurrent
tb2Months = IIf(diff < 0, 0, diff)
tbCurrent = IIf(diff < 0, diff, 0)
End If
ElseIf tb2Months < 0 Then
If tbCurrent > 0 Then
diff = tb2Months + tbCurrent
tbCurrent = IIf(diff < 0, 0, diff)
tb2Months = IIf(diff < 0, diff, 0)
End If
End If

If tb1Month > 0 Then
If tbCurrent < 0 Then
diff = tb1Month + tbCurrent
tb1Month = IIf(diff < 0, 0, diff)
tbCurrent = IIf(diff < 0, diff, 0)
End If
ElseIf tb1Month < 0 Then
If tbCurrent > 0 Then
diff = tb1Month + tbCurrent
tbCurrent = IIf(diff < 0, 0, diff)
tb1Month = IIf(diff < 0, diff, 0)
End If
End If

Select Case months
Case 3: Dues = tb3Months
Case 2: Dues = tb2Months
Case 1: Dues = tb1Month
Case 0: Dues = tbCurrent
End Select
End Function

 

[Clif McIrvin]

This code is taking totals away in the wrong order tbCurrent should be
last to be subtracted, this code is making it the first to be
subtracted....Any help thanks Bob

I only worked my way part way through the code -- what happens if
tb3Months (etc) is ZERO?

One or the other IF or ELSEIF should probably be >= or <= as
appropriate, this might be the key you are looking for.

A question / observation:

Wouldn't ByVal in the function definition accomplish the same thing as
setting up a dummy variable? (VBA's default is ByRef.)

 

[Bob Vance]

Thanks Clif if [tb3Months] is $3,000 and the value to subtract is $4,000
then $1,000 spills over into tb2Months and so on till the last text box
tbCurrent...........Thanks Bob

 

[Clif McIrvin]

Thanks Clif if [tb3Months] is $3,000 and the value to subtract is
$4,000 then $1,000 spills over into tb2Months and so on till the last
text box tbCurrent...........Thanks Bob

Hmm ... value to subtract is in tb2Months, then? Let's drop down into
the code [my remarks are in brackets] ....

Before that, though, something that took me a while to figure through.
When coding, I always attempt to never write the same code twice, if I
can find a reasonable way to avoid it. In this case, I'd attempt to use
an array, to wit:

dim myArray(0 to 3) as currency, i, j

myArray(3) = tb3Months0
myArray(2) = tb2Months0
myArray(1) = tb1Month0
myArray(0) = tbCurrent0

For i = 3 to 1 step -1
For j = i-1 to 0 step -1
if myArray(i) > 0 then
if myArray(j) < 0 then
(code)

kind of personal preference, I guess; it would make the debugging
technique I illustrate below a bit more involved, but IMO makes future
maintenance of the code much simpler.

Now, back to your question:

I illustrated a debugging technique I use below by adding [...] to the
right of statements...
HTH!

--
Clif

This code is taking totals away in the wrong order tbCurrent should
be last to be subtracted, this code is making it the first to be
subtracted....Any help thanks Bob

I only worked my way part way through the code -- what happens if
tb3Months (etc) is ZERO?

One or the other IF or ELSEIF should probably be >= or <= as
appropriate, this might be the key you are looking for.

A question / observation:

Wouldn't ByVal in the function definition accomplish the same thing
as setting up a dummy variable? (VBA's default is ByRef.)

--
Clif
Still learning Access 2003

Function Dues(tb3Months0 As Double, tb2Months0 As Currency,
tb1Month0 As Currency, tbCurrent0 As Currency, months As Integer) As
Currency
Dim tb3Months As Currency, tb2Months As Currency, tb1Month As
Currency, tbCurrent As Currency
Dim diff As Double

diff = 0
tb3Months = tb3Months0 [ 3,000]
tb2Months = tb2Months0 [-4,000]
tb1Month = tb1Month0
tbCurrent = tbCurrent0

If tb3Months > 0 Then [3,000 ==> True]
If tb2Months < 0 Then [-4,000 ==> True]
diff = tb3Months + tb2Months [-1,000]
tb3Months = IIf(diff < 0, 0, diff) [0]
tb2Months = IIf(diff < 0, diff, 0) [-1,000]
End If
ElseIf tb3Months < 0 Then [0 ==> False]
If tb2Months > 0 Then [-1000 ==> False]
diff = tb3Months + tb2Months
tb2Months = IIf(diff < 0, 0, diff)
tb3Months = IIf(diff < 0, diff, 0)
End If
End If

If tb3Months > 0 Then [0 ==> False]
If tb1Month < 0 Then
diff = tb3Months + tb1Month
tb3Months = IIf(diff < 0, 0, diff)
tb1Month = IIf(diff < 0, diff, 0)
End If
ElseIf tb3Months < 0 Then [0 ==> False]
If tb1Month > 0 Then
diff = tb3Months + tb1Month
tb1Month = IIf(diff < 0, 0, diff)
tb3Months = IIf(diff < 0, diff, 0)
End If
End If

If tb3Months > 0 Then [0 ==>False]
If tbCurrent < 0 Then
diff = tb3Months + tbCurrent
tb3Months = IIf(diff < 0, 0, diff)
tbCurrent = IIf(diff < 0, diff, 0)
End If
ElseIf tb3Months < 0 Then [0 ==>False]
If tbCurrent > 0 Then
diff = tb3Months + tbCurrent
tbCurrent = IIf(diff < 0, 0, diff)
tb3Months = IIf(diff < 0, diff, 0)
End If
End If

If tb2Months > 0 Then [-1,000 ==>
False]
If tb1Month < 0 Then
diff = tb2Months + tb1Month
tb2Months = IIf(diff < 0, 0, diff)
tb1Month = IIf(diff < 0, diff, 0)
End If
ElseIf tb2Months < 0 Then [-1,000 ==> True]
If tb1Month > 0 Then [etc, as above
.....]
diff = tb2Months + tb1Month
tb1Month = IIf(diff < 0, 0, diff)
tb2Months = IIf(diff < 0, diff, 0)
End If
End If

If tb2Months > 0 Then
If tbCurrent < 0 Then
diff = tb2Months + tbCurrent
tb2Months = IIf(diff < 0, 0, diff)
tbCurrent = IIf(diff < 0, diff, 0)
End If
ElseIf tb2Months < 0 Then
If tbCurrent > 0 Then
diff = tb2Months + tbCurrent
tbCurrent = IIf(diff < 0, 0, diff)
tb2Months = IIf(diff < 0, diff, 0)
End If
End If

If tb1Month > 0 Then
If tbCurrent < 0 Then
diff = tb1Month + tbCurrent
tb1Month = IIf(diff < 0, 0, diff)
tbCurrent = IIf(diff < 0, diff, 0)
End If
ElseIf tb1Month < 0 Then
If tbCurrent > 0 Then
diff = tb1Month + tbCurrent
tbCurrent = IIf(diff < 0, 0, diff)
tb1Month = IIf(diff < 0, diff, 0)
End If
End If

Select Case months
Case 3: Dues = tb3Months
Case 2: Dues = tb2Months
Case 1: Dues = tb1Month
Case 0: Dues = tbCurrent
End Select
End Function