Need to improve running average function

[Lucas]

Hi everyone,

I got the following function from the microsoft support site (http://support.microsoft.com/default.aspx?scid=kb;en-us;Q210138 ).

Function MovAvg(Region As String, MPG As String, startDate As Date, Period As Integer)

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

    sql = "Select * from Query1 "
    sql = sql & "where Region = '" & Region & "'"
    sql = sql & " and MPG = '" & MPG & "'"
    sql = sql & " and Period <= #" & startDate & "#"
    sql = sql & " order by Period"

    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("SumOfQTY")
        End If
        rst.MovePrevious
    Next N
    rst.Close
    MovAvg = ma / Period

End Function

Now I need to improve it a bit.

I understand how I can add new arguments to calculate it on a different level, but is it possible to change the code somehow so that it automatically detects the values it needs in the query I use it in. I tried adding an argument "Source" and changed the code to include that in the SQL but that didn't work.

Can anyone help me out?

Lucas

 

[modest]

What exactly are you trying to do?

 

[Lucas]

My problem was that I needed to somehow control over which query/table the function was run. I managed to add an extra criteria, so that has been solved. However, the performance of any query that uses this function is extremely slow for some reason. Any idea what I could to to improve this?

Lucas

P.S. Here's the updated code:

Function MovAvgRegionMPG(Region As String, MPG As String, Generation As String, _
                Source As String, startDate, Period As Integer)

    Dim rst As DAO.Recordset
    Dim sql As String
    Dim ma As Long
    Dim N As Integer

    sql = "SELECT [Data Type], Region, [SPT Generation]" & _
          ", MPG, [Forecast Horizon], Period, Sum(SumOfQTY2) AS SumOfQTY" & _
          " FROM " & Source & _
          " WHERE Region = '" & Region & "'" & _
          " AND [SPT Generation] = '" & Generation & "'" & _
          " AND MPG = '" & MPG & "'" & _
          " AND Period <= #" & startDate & "#" & _
          " GROUP BY [Data Type], Region, [SPT Generation], [Forecast Horizon]" & _
                ", MPG,Period;"

    Set rst = CurrentDb.OpenRecordset(sql)
    rst.MoveLast
    For N = 0 To Period - 1
        If rst.BOF Then
            MovAvgRegionMPG = 1E-31
            Exit Function
        Else
            ma = ma + rst.Fields("SumOfQTY")
        End If
        rst.MovePrevious
    Next N
    rst.Close
    MovAvgRegionMPG = ma / Period

End Function