Moving Average Module; Problems

[Rebecca]

Hello,

I have created the module below to calculate the moving
average by quarter for a query called qryMovAvgGoals.
This query contains 2 tables, joined by quarter. Quarter
is a field in both tables and is shown as 2001 Quarter 4,
2002 Quarter 3, etc...through the current quarter.

Within the query I have an expr1: EffortMovAvg
([SumOfactual_effort_hrs],[tblQuarterSummary].
[Quarter],4). Whenever I run the query, I get an error
message "type mismatch" and the debugger highlights the
line "set db = CurrentDb()". If I comment out this line,
I get the error message, "Object variable or With Block
variable not set".

I have no idea why the module/query are not working, and
have spent 3 days trying to figure it out. Any assistance
that you can offer would be greatly appreciated. Thanks
in advance for your help.
--------------------------------------
Option Compare Database

Option Explicit

Function EffortMovAvg(SumOfactual_effort_hrs,
quarterStart, period As Integer)

Dim rst As DAO.Recordset
Dim db As DAO.Recordset
Dim strSQL As String
Dim ma As Double
Dim n As Integer

strSQL = "Select * from qryMovAvgGoals "
strSQL = strSQL & "Where tblQuarterSummary.Quarter <= " &
quarterStart & " "
strSQL = strSQL & "Order by tblQuarterSummary.Quarter"

Set db = CurrentDb()
Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)

rst.MoveLast
For n = 0 To period - 1
If rst.BOF Then
EffortMovAvg = 0
Exit Function
Else
ma = ma + rst.Fields("SumOfactual_effort_hrs")
End If
rst.MovePrevious
Next n
rst.Close
EffortMovAvg = ma / period

End Function

 

[Dan Artuso]

Hi,
You've declared db as a recordset:
Dim db As DAO.Recordset

then you try to set it to a database object:
Set db = CurrentDb()

Hence the type mismatch.
use:
Dim db As DAO.Database