Visual Basic - Having Problem on the For Next Loop

[Nelson The Missing Lead]

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

 

[Gary Walter]

Hi Nelson,

Since this is the query newsgroup, how about
a query instead?

Assuming each record has a close date field
(say "CloseDate")

after backing up your data,
try something like following

UPDATE [SGX Individual Historical] AS T
SET T.DaysAvg14 =
DAvg("Close","SGX Individual Historical",
"[CloseDate]
BETWEEN
#" & T.CloseDate & "# - 14
AND
#" & T.CloseDate & "#");

or

UPDATE [SGX Individual Historical] AS T
SET T.DaysAvg14 =
DAvg("Close","SGX Individual Historical",
"[CloseDate]
BETWEEN
#" & T.CloseDate - 14 & "#
AND
#" & T.CloseDate & "#");

if your CloseDate has a time component,

UPDATE [SGX Individual Historical] AS T
SET T.DaysAvg14 =
DAvg("Close","SGX Individual Historical",
"[CloseDate] >=#" & T.CloseDate - 14 & "#
AND
[CloseDate] < #" & T.CloseDate + 1 & "#");

good luck,

gary

 

[Gary Walter]

sorry...forgot DateValue() in last example


if your CloseDate has a time component,

UPDATE [SGX Individual Historical] AS T
SET T.DaysAvg14 =
DAvg("Close","SGX Individual Historical",
"[CloseDate] >= #" & DateValue(T.CloseDate) - 14 & "#
AND
[CloseDate] < #" & DateValue(T.CloseDate) + 1 & "#");

good luck,

gary

Hi Nelson,

Since this is the query newsgroup, how about
a query instead?

Assuming each record has a close date field
(say "CloseDate")

after backing up your data,
try something like following

UPDATE [SGX Individual Historical] AS T
SET T.DaysAvg14 =
DAvg("Close","SGX Individual Historical",
"[CloseDate]
BETWEEN
#" & T.CloseDate & "# - 14
AND
#" & T.CloseDate & "#");

or

UPDATE [SGX Individual Historical] AS T
SET T.DaysAvg14 =
DAvg("Close","SGX Individual Historical",
"[CloseDate]
BETWEEN
#" & T.CloseDate - 14 & "#
AND
#" & T.CloseDate & "#");

if your CloseDate has a time component,

UPDATE [SGX Individual Historical] AS T
SET T.DaysAvg14 =
DAvg("Close","SGX Individual Historical",
"[CloseDate] >=#" & T.CloseDate - 14 & "#
AND
[CloseDate] < #" & T.CloseDate + 1 & "#");

good luck,

gary

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

 

[Dale Fye]

Nelson,

When you say 14 day average, are you talking about 14 consecutive days, or
14 consecutive dates in your table (might exclude weekends and holidays)?

Dale

 

[Nelson The Missing Lead]

Hi,

Can expert from visual basic offer your help, pls!!!!!!

Thank Gary & Dale, using query statement will only show a single 14days
average value based on the date you set......but using visual basic, you can
calculate all the 14days average on all records...........hi dale, dun worry
about the wkend or holidays as my code is basic on the index rather than the
date.

Please offer help......visual basic expert!!!!!!!!!!!!!!!!!!!!!

Thank you

Nelson Chou

 

[Chris2]

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.

Nelson,

Please be aware that: "[...] code seems like can't get out the For Next Loop." is vague.

From the way I am reading your message, it appears to me as if you are stating that once
you get down to 13 records left, that something goes wrong.

What happens, exactly?

1) Does the loop stop executing with an error?
2) Does the loop proceed endlessly?

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 }

This comment makes it appear that your code stops executing here.

If so, what is the error message that is generated when the code stops?

If not, why the comment right here?

intB = intB + 1
If Not rst.EOF Then
rst.MoveNext
Else
Exit For
End If
Next intA

Unless I'm mistaken, from the way this loop is constructed, if the first line of code in
the For loop is operating on the last record of the recordset, and the code execution
reaches the IF block two lines later, the True code (rst.MoveNext) will advance the
recordset to EOF, and then the For loop will proceed to the top, where the first statement
will not work because the recordset is at EOF.

When the code execution runs over "If Not rst.EOF Then", we *already* know the answer to
this question, because it was answered at "Do While Not rst.EOF" further above, and the
recordset has not yet been advanced between these two statements.

Try:

' The code must advance the recordset to try and find EOF.

rst.MoveNext

' Test if EOF has been found after advancing recordset.

If rst.EOF Then
Exit For ' Leave loop if recordset at EOF
End If

' Indent code inside of IF-blocks.


Note: Based on the information provided, I do not actually know if you are experiencing
this problem, or if following the above suggestion will solve your difficulty.


Sincerely,

Chris O.

 

[Nelson The Missing Lead]

Hi Chris,

The loop stop executing with an error appear.

Thank u, please advise me

Nelson Chou

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.

Nelson,

Please be aware that: "[...] code seems like can't get out the For Next Loop." is vague.

From the way I am reading your message, it appears to me as if you are stating that once
you get down to 13 records left, that something goes wrong.

What happens, exactly?

1) Does the loop stop executing with an error?
2) Does the loop proceed endlessly?

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 }

This comment makes it appear that your code stops executing here.

If so, what is the error message that is generated when the code stops?

If not, why the comment right here?

intB = intB + 1
If Not rst.EOF Then
rst.MoveNext
Else
Exit For
End If
Next intA

Unless I'm mistaken, from the way this loop is constructed, if the first line of code in
the For loop is operating on the last record of the recordset, and the code execution
reaches the IF block two lines later, the True code (rst.MoveNext) will advance the
recordset to EOF, and then the For loop will proceed to the top, where the first statement
will not work because the recordset is at EOF.

When the code execution runs over "If Not rst.EOF Then", we *already* know the answer to
this question, because it was answered at "Do While Not rst.EOF" further above, and the
recordset has not yet been advanced between these two statements.

Try:

' The code must advance the recordset to try and find EOF.

rst.MoveNext

' Test if EOF has been found after advancing recordset.

If rst.EOF Then
Exit For ' Leave loop if recordset at EOF
End If

' Indent code inside of IF-blocks.


Note: Based on the information provided, I do not actually know if you are experiencing
this problem, or if following the above suggestion will solve your difficulty.


Sincerely,

Chris O.

 

[Gary Walter]

Hi Nelson,

If you have come to this group
seeking the "non-error code" to do
something the "wrong way," then
so be it...

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
If Not rst.EOF Then
numAve = rst.Fields!Close + numAve
intB = intB + 1

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

good luck,

gary

 

[Chris2]

Hi Chris,

The loop stop executing with an error appear.

Thank u, please advise me

Nelson Chou

Nelson Chou,

In my previous post I wrote: "If so, what is the error message that is generated when the
code stops?"

I also wrote out an explanation of what I thought was an error in the code.

What are your comments on my explanation? Does it have any bearing on your issue?


Sincerely,

Chris O.