Seek with NoMatch and Incrementing i value MvgAvg

  • Thread starter Thread starter info
  • Start date Start date
I

info

Hello to All,

I'm re-using some code from MS kb-143237, which calcs a moving average
using seek. The code works great provided that there is data for all
the periods between the week end date and the earliest week end date.
However, when there isn't data for a particular week it errors out "no
current record". What I'd like, is to have zero added to the sum and
the i value incremented, so that it will move onto the next week and
continue averaging. I've tried using a MyRst.NoMatch but can't seem to
figure out where to add it and how to increment the i value. Can
someone suggest something please?

Here's the code:

Function MAvgs(Periods As Integer, WeekEndDate, EarliestDate)

Dim MyDB As Database
Dim MyRst As DAO.Recordset
Dim MySum As Double

Dim i, x
Set MyDB = CurrentDb()

Set MyRst = MyDB.OpenRecordset("tblLabor")

MyRst.Index = "PrimaryKey" 'WeeksSundayDate
x = Periods - 1
ReDim Store(x)
MySum = 0

For i = 0 To x
MyRst.MoveFirst
MyRst.Seek "=", WeekEndDate

Store(i) = MyRst![VarAmt]

If i <> x Then WeekEndDate = WeekEndDate - 7

If WeekEndDate < EarliestDate Then MAvgs = Null: Exit Function

MySum = Store(i) + MySum
Next i

MAvgs = MySum / Periods
MyRst.Close

Set MyDB = Nothing
Set MyRst = Nothing

End Function

tia
Mo
 
MO,

I didn't (couldn't) test the code changes, but this should work.


Function MAvgs(Periods As Integer, WeekEndDate, EarliestDate)

Dim MyDB As Database
Dim MyRst As DAO.Recordset
Dim MySum As Double

Dim i, x
Set MyDB = CurrentDb()

Set MyRst = MyDB.OpenRecordset("tblLabor")

'*** Added ******
' no records - then exit
'If MyRst.BOF And MyRst.EOF Then
' MAvgs = Null
' Exit Function
'End If
'*** Added ******

MyRst.Index = "PrimaryKey" 'WeeksSundayDate
x = Periods - 1
ReDim store(x)
MySum = 0

For i = 0 To x
MyRst.MoveFirst
MyRst.Seek "=", WeekEndDate

'*** Added ******
If MyRst.NoMatch Then
store(i) = 0
Else
store(i) = MyRst![VarAmt]
End If
'*** Added ******

If i <> x Then WeekEndDate = WeekEndDate - 7
If WeekEndDate < EarliestDate Then
MAvgs = Null
Exit Function
End If
MySum = store(i) + MySum
Next i

MAvgs = MySum / Periods
MyRst.Close

Set MyDB = Nothing
Set MyRst = Nothing

End Function


HTH
 
See Code Mods Below:

[email protected] said:
Hello to All,

I'm re-using some code from MS kb-143237, which calcs a moving average
using seek. The code works great provided that there is data for all
the periods between the week end date and the earliest week end date.
However, when there isn't data for a particular week it errors out "no
current record". What I'd like, is to have zero added to the sum and
the i value incremented, so that it will move onto the next week and
continue averaging. I've tried using a MyRst.NoMatch but can't seem to
figure out where to add it and how to increment the i value. Can
someone suggest something please?

Here's the code:

Function MAvgs(Periods As Integer, WeekEndDate, EarliestDate)

Dim MyDB As Database
Dim MyRst As DAO.Recordset
Dim MySum As Double

Dim i, x
Set MyDB = CurrentDb()

Set MyRst = MyDB.OpenRecordset("tblLabor")

MyRst.Index = "PrimaryKey" 'WeeksSundayDate
x = Periods - 1
ReDim Store(x)
MySum = 0

For i = 0 To x
MyRst.MoveFirst
MyRst.Seek "=", WeekEndDate
Click to expand...
If MyRst.NoMatch = True Then
Store(i) = 0
Else
Store(i) = MyRst![VarAmt] End If

If i <> x Then WeekEndDate = WeekEndDate - 7

If WeekEndDate < EarliestDate Then MAvgs = Null: Exit Function

MySum = Store(i) + MySum
Next i

MAvgs = MySum / Periods
MyRst.Close

Set MyDB = Nothing
Set MyRst = Nothing

End Function

tia
Mo
Click to expand...
 
Steve - thank you for your reply - I like the no records addition test
- hadn't even thought of that - also, I see the addition for moving
onto the next week. I'll give this a try.

Thanks again.
 
Back
Top