visual basic - retrieve values from table and perform calculation

[Guest]

Hi,

I trying to retrieve values from a table to calculate the 14days average
value of a stock closing price. However, i encounter some problem as stated
beside the code as follows:

Function DaysAvgs()
'Calculate the average value of a given value.

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim varBookmark As Variant
Dim numAve, numDaysAvg As Double
Dim intA, intB, lngCount As Integer

Set db = CurrentDb

'Open Table
Set rst = db.OpenRecordset("SGX Individual Historical", dbOpenTable)

rst.MoveFirst

Do While Not rst.EOF
intA = 1
intB = 0
varBookmark = rst.Bookmark
numDaysAvg = 0
numAve = 0

For intA = 1 To 14
numAve = rst.Fields!Close + numAve { When run, can't find record }
intB = intB + 1
If Not rst.EOF Then
rst.MoveNext
Else
intA = 15
End If
Next intA

rst.Bookmark = varBookmark
numDaysAvg = numAve / intB
rst.Edit
rst.Fields!DaysAvg14 = numDaysAvg
rst.Update
rst.MoveNext
Loop

Set rst = Nothing
Set db = Nothing

End Function

Please help. Thank

Nelson Chou

 

[Guest]

Hi,

I have manage to locate the fault on the For and Next statement:

For intA = 1 To 14

numAve = rst.Fields!Close + numAve { When run, can't find record }
intB = intB + 1
If Not rst.EOF Then
rst.MoveNext
Else
intA = 15
End If
Next intA

The problem encounter problem when there left only 13records in the table.
But i thought i have this statement "If Not rst.EOF then rst.MoveNext Else
intA = 15" to exit the loop. It can't work........even i rewrite it with Else
Exit For (instead of intA = 15).

Any help.

Thank.

Nelson