Create financial month

[Gerrit.B]

I have a table with date. With an query i calaculate all others like year, week, day, etc....
How can i create financial months like 4 weeks, 4 weeks, 5 weeks, 4 weeks, 4 weeks, 5 weeks,4 weeks, 4 weeks, 5 weeks,4 weeks, 4 weeks, 5 weeks where week 53 belongs to week 1 of next year.

 

[pritcham]

Hi

Depends on what you want to do with them once you've created them to be honest. You could put together a Function to return a 'financial month' depending on the date passed to it and use the value returned in queries etc (see below for an idea on this) but like I say - it really depends on what you're trying to achieve.

Anyway, you could have a function to work out what the financial month is for a given date like this:

Public Function FinancialMonth(myDate as Date) As Integer
Select Case DatePart("ww",myDate)
Case 1 to 4:
FinancialMonth = 1
Case 5 to 10:
FinancialMonth = 2
.....etc etc
..
..
..
End Case
End Function

This would (once you've written it fully) return the financial month that you designate via the code depending on what week number it is in the year. This value could then be used in your selects etc

(e.g. Select * from myTable Where DatePart("m",myDate) = FinancialMonth("01/Feb/2005")

Hope that helps you.

Martin

 

[Gerrit.B]

Code:

************************************************

Public Function FinancialMonth(myDate As Date) As Integer
Select Case DatePart("ww", myDate)

Case 1 To 4:
FinancialMonth = 1
Case 5 To 8:
FinancialMonth = 2
Case 9 To 13:
FinancialMonth = 3
Case 14 To 17:
FinancialMonth = 4
Case 18 To 21:
FinancialMonth = 5
Case 22 To 26:
FinancialMonth = 6
Case 27 To 30:
FinancialMonth = 7
Case 31 To 34:
FinancialMonth = 8
Case 35 To 39:
FinancialMonth = 9
Case 40 To 43:
FinancialMonth = 10
Case 44 To 47:
FinancialMonth = 11
Case 48 To 52:
FinancialMonth = 12
Case 53 To 53:
FinancialMonth = 1
End Select

End Function

************************************************

Thanks, This Works Great.

Had to changed "End Case" to "End Select"

 

[pritcham]

Yes - sorry about that, I realised after I'd hit the "Post" button but thought you'd spot it anyway.

Glad you got it sorted
Martin

 

[Gerrit.B]

Can this code below changed from date to weight.
example:
0 to 1 kg = 1
1 to 5 kg = 2
etc.

Code:

************************************************

Public Function FinancialMonth(myDate As Date) As Integer
Select Case DatePart("ww", myDate)

Case 1 To 4:
FinancialMonth = 1
Case 5 To 8:
FinancialMonth = 2
Case 9 To 13:
FinancialMonth = 3
Case 14 To 17:
FinancialMonth = 4
Case 18 To 21:
FinancialMonth = 5
Case 22 To 26:
FinancialMonth = 6
Case 27 To 30:
FinancialMonth = 7
Case 31 To 34:
FinancialMonth = 8
Case 35 To 39:
FinancialMonth = 9
Case 40 To 43:
FinancialMonth = 10
Case 44 To 47:
FinancialMonth = 11
Case 48 To 52:
FinancialMonth = 12
Case 53 To 53:
FinancialMonth = 1
End Select

End Function

************************************************

 

[pritcham]

Yes - just use the same method replacing the "Case xxx" etc accordingly.

Martin

 

[Gerrit.B]

Public Function FinancialMonth(myDate As Date) As Integer
Select Case DatePart("ww", myDate)

What should i change in part above.

 

[pritcham]

Hi

Where you've got
Public Function FinancialMonth(myDate As Date) As Integer
Select Case DatePart("ww", myDate)

at the moment, change it to something like

Public Function WeightFunction(myWeight As Integer) As Integer
Select Case myWeight
Case 0 to 1:
WeightFunction=1
Case 1-5:
WeightFunction=2
Case 6:
WeightFunction=3
etc etc..

HTH
Martin