Calculating Rolling Usage

[Guest]

Hi There,

I have a funtion that I need to create to calculate rolling usage and
weighted usages in planning inventory.

I have a table called Weights which has a MonthID as a Primary Key:

MonthID Weight
1 3
2 2.5
3 2
4 1.5
5 1
6 0.75
7 0.5
8 0.25
9 0.2
10 0.15
11 0.1
12 0.05

The month ID will always apply to the previous month to this month.
Say date() = March 06, therefore Month 1 would be Feb 06

I now have to return a value of how many business days in the previous month
to this month ie - workdays(date()- MonthID, date(),0)

1) The first problem is telling access to calculate the MonthID as last
month and not as days, as all return the same current month.

This needs to be fluid as is used as a rolling usage.

I first need to make sure that Date() is formatted as a date:

So far I have copied this Module and tried to adapt it; which is is being
used in my query to return a value. However the value is not returning the
number of business days between the months.


Option Compare Database

'*********** Code Start **************
Public Function WorkingDays(StartDate As Date, EndDate As Date) As Integer
'....................................................................
' Name: WorkingDays
' Inputs: StartDate As Date
' EndDate As Date
' Returns: Integer
' Author: Arvin Meyer
' Date: February 19, 1997
' Comment: Accepts two dates and returns the number of weekdays between them
' Note that this function does not account for holidays.
'....................................................................
On Error GoTo Err_WorkingDays

Dim intCount As Integer

StartDate = Format(StartDate, "mm", vbSunday, vbFirstFullWeek)
EndDate = Format(EndDate, "mm", vbSunday, vbFirstFullWeek)
'If you want to count the day of StartDate as the 1st day
'Comment out the line above

intCount = 0
Do While StartDate <= EndDate
'Make the above < and not <= to not count the EndDate

Select Case Weekday(StartDate)
Case Is = 1, 7
intCount = intCount
Case Is = 2, 3, 4, 5, 6
intCount = intCount + 1
End Select
StartDate = StartDate + 1
Loop
WorkingDays = intCount

Exit_WorkingDays:
Exit Function

Err_WorkingDays:
Select Case Err

Case Else
MsgBox Err.Description
Resume Exit_WorkingDays
End Select

End Function

Can anyone help? I am sure if I were to fiddle with this code it would work.

Thanks in advance.

Andi

 

[Guest]

Ok I answered my own question,

This is what I was after:

Weight MonthID MonthAgo StartDate
EndDate BusinessDays
0 21/03/2006 28/02/2006 31/03/2006 23
3 1 21/02/2006 31/01/2006 28/02/2006 20
2.5 2 21/01/2006 31/12/2005 31/01/2006 22
2 3 21/12/2005 30/11/2006 31/12/2006 21
1.5 4 21/11/2005 31/10/2006 30/11/2006 22
1 5 21/10/2005 30/09/2006 31/10/2006 22
0.75 6 21/09/2005 31/08/2006 30/09/2006 21
0.5 7 21/08/2005 31/07/2006 31/08/2006 23
0.25 8 21/07/2005 30/06/2006 31/07/2006 21
0.2 9 21/06/2005 31/05/2006 30/06/2006 22
0.15 10 21/05/2005 30/04/2006 31/05/2006 23
0.1 11 21/04/2005 31/03/2006 30/04/2006 20
0.05 12 21/03/2005 28/02/2006 31/03/2006 23

The SQL:

SELECT RollingUsageWeights.Weight, RollingUsageWeights.MonthID,
DateAdd("m",-[MonthID],Date()) AS MonthAgo,
DateSerial(Year(Date()),Month(DateAdd("m",-[MonthID],Date())),0) AS
StartDate, DateSerial(Year(Date()),Month(DateAdd("m",-[MonthID],Date()))+1,0)
AS EndDate,
WorkingDays(DateSerial(Year(Date()),Month(DateAdd("m",-[MonthID],Date())),0),DateSerial(Year(Date()),Month(DateAdd("m",-[MonthID],Date()))+1,0))
AS BusinessDays
FROM RollingUsageWeights;

The Correct VB for calculating Rolling Business Days:


'*********** Code Start **************
Public Function WorkingDays(StartDate As Date, EndDate As Date) As Integer
'....................................................................
' Name: WorkingDays
' Inputs: StartDate As Date
' EndDate As Date
' Returns: Integer
' Author: Arvin Meyer
' Date: February 19, 1997
' Comment: Accepts two dates and returns the number of weekdays between them
' Note that this function does not account for holidays.
'....................................................................
On Error GoTo Err_WorkingDays

Dim intCount As Integer

StartDate = StartDate + 1
'If you want to count the day of StartDate as the 1st day
'Comment out the line above

intCount = 0
Do While StartDate <= EndDate
'Make the above < and not <= to not count the EndDate

Select Case Weekday(StartDate)
Case Is = 1, 7
intCount = intCount
Case Is = 2, 3, 4, 5, 6
intCount = intCount + 1
End Select
StartDate = StartDate + 1
Loop
WorkingDays = intCount

Exit_WorkingDays:
Exit Function

Err_WorkingDays:
Select Case Err

Case Else
MsgBox Err.Description
Resume Exit_WorkingDays
End Select

End Function