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

nebula13

New Member
Joined
Nov 20, 2009
Messages
19
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
 

Some videos you may like

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

jackd

Well-known Member
Joined
Oct 19, 2006
Messages
1,299
Office Version
  1. 365
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

New Member
Joined
Nov 20, 2009
Messages
19
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

New Member
Joined
Nov 20, 2009
Messages
19
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

Well-known Member
Joined
Oct 19, 2006
Messages
1,299
Office Version
  1. 365

ADVERTISEMENT

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

New Member
Joined
Nov 20, 2009
Messages
19
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

Well-known Member
Joined
Oct 19, 2006
Messages
1,299
Office Version
  1. 365

ADVERTISEMENT

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
 
Last edited:

nebula13

New Member
Joined
Nov 20, 2009
Messages
19
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

New Member
Joined
Nov 20, 2009
Messages
19
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

Well-known Member
Joined
Oct 19, 2006
Messages
1,299
Office Version
  1. 365
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
Code:
result = result + rs!Volume2009
 

Watch MrExcel Video

Forum statistics

Threads
1,122,657
Messages
5,597,391
Members
414,142
Latest member
Banyangt

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top