Need help w/ defining recordset for "Last 5 days average volume" function

[nebula13]

Hello Everyone,
I need to create a field in a report that calculates the average of the last 5 records (from the [volume2009] field) in the report. The report is based on a query called qry_Daily_Volume. I figured the best way to do this would be to create a VBA function ("Last5DaysAvg")to move to the last record (MoveLast)of the dataset and then loop to sum up each of previous (MovePrevious) records, thus getting the total of the last 5 records and then dividing that by 5. The resulting number would then be displayed in the footer of a report which lists daily volume to show the average of the last 5 records.

Unfortunately my function does not seem to work, and I believe the problem is with defining a record set.

Here is the code I have so far, which does not work:

Function CalcLastFive() As Double
Dim result As Double
result = 0
Dim intI, rs As DAO.Recordset
intI = 1
Set rs = db.OpenRecordset("qry_daily_volume", dbOpenDynaset)
MoveLast
Do
If intI > 5 Then Exit Do
result = result + Volume2009
intI = intI + 1
MovePrevious
Loop
result = result / 5
CalcLastFive = result
End Function


Thanks in advance for taking a look at this issue, I would greatly appreciate any input from this wonderful forum

All the best,
Ben

 

[jackd]

Hello Everyone,
I need to create a field in a report that calculates the average of the last 5 records (from the [volume2009] field) in the report. The report is based on a query called qry_Daily_Volume. I figured the best way to do this would be to create a VBA function ("Last5DaysAvg")to move to the last record (MoveLast)of the dataset and then loop to sum up each of previous (MovePrevious) records, thus getting the total of the last 5 records and then dividing that by 5. The resulting number would then be displayed in the footer of a report which lists daily volume to show the average of the last 5 records.

Unfortunately my function does not seem to work, and I believe the problem is with defining a record set.

Here is the code I have so far, which does not work:

Function CalcLastFive() As Double
Dim result As Double
result = 0
Dim intI, rs As DAO.Recordset
intI = 1
Set rs = db.OpenRecordset("qry_daily_volume", dbOpenDynaset)
MoveLast
Do
If intI > 5 Then Exit Do
result = result + Volume2009
intI = intI + 1
MovePrevious
Loop
result = result / 5
CalcLastFive = result
End Function


Thanks in advance for taking a look at this issue, I would greatly appreciate any input from this wonderful forum

All the best,
Ben

Ben,

Here's a quick look at your code.

Function CalcLastFive() As Double
Dim result As Double
result = 0
Dim intI, rs As DAO.Recordset
intI = 1
Dim db As DAO.Database
Set db = CurrentDb
Set rs = db.OpenRecordset("qry_daily_volume", dbOpenDynaset)
rs.MoveLast
Do
If intI > 5 Then Exit Do
result = result + rs!Volume2009
intI = intI + 1
rs.MovePrevious
Loop
result = result / 5
CalcLastFive = result
End Function


Good luck. I have to go out for a few hours. Will check back later.

 

[nebula13]

It still does not work. In the report I entered the last 5 days avg field's control source as "=CalcLastFive()"
I am not sure how to test this out or figure out whats causing the problem, but ideally the function would allow you to specify the field used ( e.g. control source: =CalcLastFive([volume2009]) )

Thanks
Ben

 

[nebula13]

I am pretty sure the error is being caused in the process of defining the record set. When I run the code, it generates an error before it even gets to the MoveLast clause.

I am looking into it, but it seems that the issue is in these lines:

Dim db As DAO.Database
Set db = CurrentDb
Set rs = db.OpenRecordset("qry_daily_volume", dbOpenDynaset)

Any ideas?

Thank you,
Ben

 

[jackd]

I am pretty sure the error is being caused in the process of defining the record set. When I run the code, it generates an error before it even gets to the MoveLast clause.

I am looking into it, but it seems that the issue is in these lines:

Dim db As DAO.Database
Set db = CurrentDb
Set rs = db.OpenRecordset("qry_daily_volume", dbOpenDynaset)

Any ideas?

Thank you,
Ben

Did you try my code. Your original code had some errors in recordset usage stuff.

Jack

 

[nebula13]

yeah, my last post used your code but i could not get it to work. I am not exactly sure how to fix it though...

 

[jackd]

yeah, my last post used your code but i could not get it to work. I am not exactly sure how to fix it though...

I tested the function with my data and it worked for me. However, I have a different query, different data etc.

Do you get a specific error?

I just created a form with a text box and control source =CalcLastFive() and it works fine???


Just looked at your post again. You do not have a Dim rs as dao.recordset

 

[nebula13]

you are absolutly right! your code worked perfectly when I ran it against a regular table with the same data. but for some reason it does not like my query.

What do you think might be different in the code when I use a query, rather than a table??? Do I have to change something about this line:
result = result + rs!Volume2009

Thank you,
Ben

 

[nebula13]

So I think I figured out what the issue is, but have not come up with a good solution.

The OpenRecordset function does not seem to work with Queries, but works fine with tables. Although if you add parameters to a table, doesnt that make it a query? Im not sure if this is specific to my query or to all queries in general, but I am at a loss as to how to get the last 5 days avg function to work...

Bless you for trying to help

Ben

 

[jackd]

So I think I figured out what the issue is, but have not come up with a good solution.

The OpenRecordset function does not seem to work with Queries, but works fine with tables. Although if you add parameters to a table, doesnt that make it a query? Im not sure if this is specific to my query or to all queries in general, but I am at a loss as to how to get the last 5 days avg function to work...

Bless you for trying to help

Ben

I ran your function using a query. But I did not have parameters-- what parameters are you using?
Can you paste the SQL for your query?

There is nothing wrong with this line

result = result + rs!Volume2009