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

 

[nebula13]

Hi Jack,
Let me try to explain; I have a table with all the volume data in it as well as an appID for the application (there are 3 total apps), and a LogDate. In the query, both those fields are controlled ba form where the user selects a date range and the application.
So the reason I want to use a query is because it is already has those parameters in place, as opposed to using a table.
The alternative is to run the VBA code against the root table and just add those parameters to the table (see below)

LogDate Between [Forms]![Volume_Reports]![Start] And [Forms]!
[Volume_Reports]![end]

AppID = [Forms]![Volume_Reports]![ApplicationID]

The problem is that I cant seem to get either alternative to work. Would you know how to properly include the parameters above in the code you already responded with?

Thanks, you are the best!
Ben

 

[jackd]

Hi Jack,
Let me try to explain; I have a table with all the volume data in it as well as an appID for the application (there are 3 total apps), and a LogDate. In the query, both those fields are controlled ba form where the user selects a date range and the application.
So the reason I want to use a query is because it is already has those parameters in place, as opposed to using a table.
The alternative is to run the VBA code against the root table and just add those parameters to the table (see below)

LogDate Between [Forms]![Volume_Reports]![Start] And [Forms]!
[Volume_Reports]![end]

AppID = [Forms]![Volume_Reports]![ApplicationID]

The problem is that I cant seem to get either alternative to work. Would you know how to properly include the parameters above in the code you already responded with?

Thanks, you are the best!
Ben

Please provide the SQL for the query. Perhaps we can adjust the overall query code to
execute based on a button on the Form.

 

[nebula13]

Hi Jack,
You know I actually found a much much easier way of resolving this issue! It turns out that if you create a seperate field that only captures values (volume) based on the date (logdate) being greater than or equal to 6 days before the User-defined end date.
Interestingly enough, this meathod always pulls the last 5 records since there is only volume reported on weekdays. Then all I had to do was find the average in the report.

Here is the variable I created to only pull the last 5 records:

Last5DaysVolume: IIf([LogDate]>=DateAdd("d",-6,[Forms]![Volume_Reports]![end]),[Volume2009],Null)

I know this does not answer the issue of why the VBA code didnt work for a query, but I am just relieved to have solved the original problem.

Thanks for all your help!

 

[jackd]

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

Ben, I'm glad you resolved your issue.
I was responding to another question when I thought about your mentioning parameters in the query. If you're interested, here is a post that the poster resolved by using values on his form to place values in the parameters in his query. You might see something that could be used in future.

http://www.mrexcel.com/forum/showthread.php?p=2157405#post2157405

Good luck.