How to exit the For Next Loop.....for the following code attach

[Guest]

Hi,

I trying to retrieve values from a table to calculate the 14days average
value of a stock closing price. However, i encounter problem when there is
13records left on the table, my code seems like can't get out the For Next
Loop.

Please help to advise me!!!!!!!!!!!!!!!!!!!!1

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 { Please help, can't get out loop }
intB = intB + 1
If Not rst.EOF Then
rst.MoveNext
Else
Exit For
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

 

[Marshall Barton]

I trying to retrieve values from a table to calculate the 14days average
value of a stock closing price. However, i encounter problem when there is
13records left on the table, my code seems like can't get out the For Next
Loop.
[snip]
For intA = 1 To 14
numAve = rst.Fields!Close + numAve { Please help, can't get out loop }
intB = intB + 1
If Not rst.EOF Then
rst.MoveNext
Else
Exit For
End If
Next intA

[snip]


You need to do the MoveNext before checking for EOF.

For intA = 1 To 14
numAve = rst.Fields!Close + numAve
intB = intB + 1
rst.MoveNext
If rst.EOF Then Exit For
Next intA

I believe that IntA and IntB always have the same value and
suggest that you can get rid of the intB variable. Think
about it before doing it.