Math help needed.

[Guest]

Here is the code I have. The FillArray procedure loads up an array with
values from a Record set on a Tabular form. (Thanks to Carl Colijn for
helping me get the FillArray set up.)

Private Sub FillArray()
Dim RecordNr As Long
If Not Recordset.EOF And Not Recordset.BOF Then
Recordset.MoveLast
End If
If Recordset.RecordCount > 0 Then
ReDim ValArr(Recordset.RecordCount)

Recordset.MoveFirst
RecordNr = 0
While Not Recordset.EOF
ValArr(RecordNr) = Margin
RecordNr = RecordNr + 1
Recordset.MoveNext
Wend
End If
End Sub

Here is the ArrMath procedure it works good upto the last Record set in
which it displays that Record sets Margin value as a negative number when I
need it subtracted from the previous Record sets Margin value, and then
display the proper value in the final Record sets Net_Change box.

Private Sub ArrMath()
Dim RecordNr As Long
RecordNr = 0
Recordset.MoveFirst
Net_Change = ValArr(0)
For RecordNr = 1 To UBound(ValArr)
Recordset.MoveNext
Net_Change = ValArr(RecordNr) - ValArr(RecordNr - 1)
Next
End Sub

How it displaying on the form is like this:

Margin Net_Change
500 +500
1000 +500
1200 -1200

It should look like this:

Margin Net_Change
500 +500
1000 +500
1200 +200

Any more help would, and is greatly appreciated.

C_Ascheman

 

[Guest]

Hi C -- Try changing the For condition to:

For RecordNr = LBound(ValArr) To UBound(ValArr)
Recordset.MoveNext
Net_Change = ValArr(RecordNr) - ValArr(RecordNr - 1)
Next

Let me know if this works for you.

 

[Guest]

Nope that didn't work. Gave me a subscript out of range on:
Net_Change = ValArr(RecordNr) - ValArr(RecordNr - 1)
Changed:
For RecordNr = LBound(ValArr) To UBound(ValArr)
to
For RecordNr = LBound(ValArr) + 1 To UBound(ValArr)
No more subscript out of range, but still same problem. Last Recordset is
displaying the Margin value in the Net_Change field as a negative number.

 

[Guest]

Interesting...another thing I noticed is that you have code to move through
the recordset in the math function but you have previously filled an array
with this data so you shouldn't need these lines anymore. Your code should
look like:

Private Sub ArrMath()

Dim RecordNr As Long
RecordNr = 0

Net_Change = ValArr(0)
For RecordNr = 1 To UBound(ValArr)
Net_Change = ValArr(RecordNr) - ValArr(RecordNr - 1)
Next RecordNr

End Sub

Add a debug line to see what values are being calculated and step through
the code:

Private Sub ArrMath()
Dim RecordNr As Long
RecordNr = 0

Net_Change = ValArr(0)
For RecordNr = 1 To UBound(ValArr)
Net_Change = ValArr(RecordNr) - ValArr(RecordNr - 1)
Debug.print "Record: " & RecordNR & "=" & ValArr(RecordNr) & "-" &
ValArr(RecordNr - 1)
Next
End Sub

This will give you an idea of why the calculation is off. Without testing
it myself it's difficult to pinpoint the error.

 

[Guest]

Ok finally its working correctly. Here is what I have now, and it works.

Private Sub ArrMath()
Dim RecordNr As Long
RecordNr = 0

Net_Change = ValArr(0)
For RecordNr = 1 To UBound(ValArr) - 1
Net_Change = ValArr(RecordNr) - ValArr(RecordNr - 1)
Next
End Sub

Had to change the UBOUND(ValArr) to UBOUND(ValArr) - 1. When it was running
through the last bit of code it was going 1 to far so basically it was
putting the result of 0 - 1500 in the last column. Now I get the proper
amount of +500 (1500 - 1000). Thanks xRoachx for all your help.

C_Ascheman

 

[Guest]

Great! Glad I could help.

Ok finally its working correctly. Here is what I have now, and it works.

Private Sub ArrMath()
Dim RecordNr As Long
RecordNr = 0

Net_Change = ValArr(0)
For RecordNr = 1 To UBound(ValArr) - 1
Net_Change = ValArr(RecordNr) - ValArr(RecordNr - 1)
Next
End Sub

Had to change the UBOUND(ValArr) to UBOUND(ValArr) - 1. When it was running
through the last bit of code it was going 1 to far so basically it was
putting the result of 0 - 1500 in the last column. Now I get the proper
amount of +500 (1500 - 1000). Thanks xRoachx for all your help.

C_Ascheman