Undefined function "MovAvg" in expression

  • Thread starter Thread starter Rebecca
  • Start date Start date
R

Rebecca

Hello,

I created a module called MovAvg to calculate the moving
average of the past 3 weeks worth of data. I then created
a query and entered an expression, to tell the query to
average the past 3 weeks worth of data where the currency
type and the transaction date are the same. When I try to
run the query, I get the error message "Undefined
function "MovAvg" in Expression". Can you please help me
so that this will work. I have a much more complex moving
average to calculate once I figure this problem out.
Thanks!

My module code looks like this:

Option Explicit

'The following function MovAvg computes moving averages
based on a table with a multiple-field primary key.

Function MovAvg(currencyType, startDate, period As Integer)

Dim rst As DAO.Recordset
Dim sql As String
Dim ma As Currency

sql = "Select * from table1 "
sql = sql & "where currencyType = '" & currencyType
& "'"
sql = sql & " and transactiondate <= #" & startDate
& "#"
sql = sql & " order by transactiondate"

Set rst = CurrentDb.OpenRecordset(sql)
rst.MoveLast
For n = 0 To period - 1
If rst.BOF Then
MovAvg = 0
Exit Function
Else
ma = ma + rst.Fields("rate")
End If
rst.MovePrevious
Next n
rst.Close
MovAvg = ma / period

End Function

The query expression looks like:
Expr1: MovAvg([CurrencyType],[TransactionDate],3)
 
I created a module called MovAvg to calculate the moving
average of the past 3 weeks worth of data.
Click to expand...

That's the problem: the name of the module. The Module must have a
name DIFFERENT from that of any sub or function.

Rename the module to basMovAvg and you should be in good shape.
 
Thanks John, that worked!
-----Original Message-----


That's the problem: the name of the module. The Module must have a
name DIFFERENT from that of any sub or function.

Rename the module to basMovAvg and you should be in good shape.


.
Click to expand...
 
Back
Top