Moving Average

[mjdavie]

Could somebody please help me?

I am using the following code (from MS Knowledge Base) in a query which works well when I have only 2 variables. However I have tried to adapt the code to use 3 variables as I have 3 Primary keys in my table but the result I get is always a 0. Does anybody have any idea on how I can resolve this

Thanks in advance

Function MAvgs(Periods As Integer, StartDate, TypeName, Model)
Dim db As Database, MyRST As Recordset, MySum As Double
Dim i, x
Set db = CurrentDb()
Set MyRST = db.OpenRecordset("tblUpdateMill")

On Error Resume Next

MyRST.Index = "PrimaryKey"
x = Periods - 1
ReDim Store(x)
MySum = 0

For i = 0 To x
MyRST.MoveFirst
MyRST.Seek "=", StartDate, TypeName, Model

' These two variables should be in the same order as the
' primary key fields in your table.
Store(i) = MyRST![Rate]

If i <> x Then StartDate = StartDate - 7
' The 7 here assumes weekly data; 1 for daily data.

If StartDate < #8/6/1993# Then MAvgs = Null: Exit Function
' #8/6/93# is replaced with the earliest date of the data
' in your table.
MySum = Store(i) + MySum
Next i

MAvgs = MySum / Periods
MyRST.Close

End Function