A
Andi Lee Davis
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
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