Calendar year versus Fiscal year  | Familiar Sight | | Join Date: Aug 2006 Location: Grand Junction, CO
Posts: 233
| | |
I have a query that puts dates into quarters ofr me. The problem is that it puts it into jan-mar as quarter one. I need jul-sep of 2008 to be q1 2009 and jan-mar to be q3 2009. Any ideas?
|  | Expert | | Join Date: Apr 2006 Location: Philadelphia
Posts: 5,218
| | | re: Calendar year versus Fiscal year
Quote:
Originally Posted by rcollins  I have a query that puts dates into quarters ofr me. The problem is that it puts it into jan-mar as quarter one. I need jul-sep of 2008 to be q1 2009 and jan-mar to be q3 2009. Any ideas? You can use the combination of a Query with a Calculated Field, that returns the proper Quarter via a Public Function. I used the Orders Table of the Northwind Database for this demo: - Query with Calculated Field:
-
SELECT Orders.ShippedDate, fCalcQuarters([ShippedDate]) AS Quarter
-
FROM Orders
-
WHERE (((Orders.ShippedDate) Between #6/1/1997# And #5/31/1998#))
-
ORDER BY fCalcQuarters([ShippedDate]);
-
- Function Definition:
-
Public Function fCalcQuarters(dteShipDate As Date)
-
If dteShipDate >= #6/1/1997# And dteShipDate <= #8/31/1997# Then
-
fCalcQuarters = "Q1"
-
ElseIf dteShipDate >= #9/1/1997# And dteShipDate <= #11/30/1997# Then
-
fCalcQuarters = "Q2"
-
ElseIf dteShipDate >= #12/1/1997# And dteShipDate <= #2/28/1998# Then
-
fCalcQuarters = "Q3"
-
ElseIf dteShipDate >= #3/1/1998# And dteShipDate <= #5/30/1998# Then
-
fCalcQuarters = "Q4"
-
Else
-
fCalcQuarters = Null
-
End If
-
End Function
-
- Sample Output:
| | Familiar Sight | | Join Date: Aug 2006 Location: Grand Junction, CO
Posts: 233
| | | re: Calendar year versus Fiscal year
So, I think I understand this to the most part, but I see an issue or two. First, Where to I keep the function? do I do this in a module? Also, This seems to be year specific. I would like to be able to use a criteria form for the report, will I have to keep changing dates or is there a way to do this for any year? What about the fact that july through dec 2008 are actually part of fiscal year 2009? it seems that this query would still list these months in the wrong year.
Thanks for all your help, you are very appriciated
| | Expert | | Join Date: Apr 2006 Location: Philadelphia
Posts: 5,218
| | | re: Calendar year versus Fiscal year
Quote:
Originally Posted by rcollins So, I think I understand this to the most part, but I see an issue or two. First, Where to I keep the function? do I do this in a module? Also, This seems to be year specific. I would like to be able to use a criteria form for the report, will I have to keep changing dates or is there a way to do this for any year? What about the fact that july through dec 2008 are actually part of fiscal year 2009? it seems that this query would still list these months in the wrong year.
Thanks for all your help, you are very appriciated Quote:
Where to I keep the function?
In a Standard Code Module Quote:
Do I do this in a module?
Previously answered Quote:
I would like to be able to use a criteria form for the report, will I have to keep changing dates or is there a way to do this for any year?
It can probably be converted to accept any Year Quote:
What about the fact that july through dec 2008 are actually part of fiscal year 2009? it seems that this query would still list these months in the wrong year.
Maybe I'm confused, (fiscally illerate), but isn't July 2008 considered to be Quarter 1 of Fiscal Year 2009, and isn't December 2008 considered to be Quarter 3 of Fiscal Year 2009? P.S. - Kindly Reply with specifics.
| | Moderator | | Join Date: Feb 2008 Location: Beauly, near Inverness, Scotland
Posts: 1,576
| | | re: Calendar year versus Fiscal year
Hi. ADezii has shown a query which can with a few adjustments be generalised to work with any year to return a quarter value. It appears to me that what you are really asking for is slightly different - a way to return a year and quarter reference, as you mention being able to show the fiscal year as part of the value.
The function below is an adaptation of one I use to return fiscal year quarters. Like the one ADezii gave you this has to be located in any public code module - one that is shown in the Modules tab within Access. If there are none at present just create a new one and paste the code into the module then save it under any suitable name (not the name of the function, though!).
In the query editor you can use the function to return the quarter reference given the transaction date, as a calculated field set along the lines of
FiscalQuarter: fYearQuarter([your transaction date])
The function returns the quarter year-first to ensure that results can be ordered in ascending or descending order correctly. - Public Function fYearQuarter(somedate, Optional quarter_start = 3) As String
-
'Returns the current financial year quarter for the current financial year
-
'in the form yyyy-Qn where yyyy is the financial year and n is the
-
'quarter number.
-
'Assumes that the financial year starts in quarter 3 of the calendar year
-
'which can be varied by changing the default quarter_start value
-
Dim intQ As Integer, intYear As Integer
-
If Not IsNull(somedate) Then
-
intYear = Year(somedate)
-
Select Case quarter_start
-
Case 1 To 4
-
intQ = CLng(Format(somedate, "q")) - quarter_start + 1
-
If intQ <= 0 Then
-
intQ = intQ + 4
-
intYear = intYear - 1
-
End If
-
fYearQuarter = intYear & "-Q" & intQ
-
Case Else
-
fYearQuarter = "invalid start quarter"
-
End Select
-
End If
-
End Function
Test data (uk date format - dd/mm/yyyy) - Date Quarter
-
01/07/2008 2008-Q1
-
01/08/2008 2008-Q1
-
01/09/2008 2008-Q1
-
01/10/2008 2008-Q2
-
01/11/2008 2008-Q2
-
01/12/2008 2008-Q2
-
01/01/2009 2008-Q3
-
01/02/2009 2008-Q3
-
01/03/2009 2008-Q3
-
01/04/2009 2008-Q4
-
01/05/2009 2008-Q4
-
01/06/2009 2008-Q4
-
01/07/2009 2009-Q1
-
01/08/2009 2009-Q1
-
01/09/2009 2009-Q1
-
01/10/2009 2009-Q2
-
01/11/2009 2009-Q2
-
01/12/2009 2009-Q2
-
01/01/2010 2009-Q3
-
01/02/2010 2009-Q3
-
01/03/2010 2009-Q3
-
01/04/2010 2009-Q4
-
01/05/2010 2009-Q4
-
01/06/2010 2009-Q4
-
01/07/2010 2010-Q1
-
01/08/2010 2010-Q1
-
01/09/2010 2010-Q1
-
01/10/2010 2010-Q2
-
01/11/2010 2010-Q2
-
01/12/2010 2010-Q2
-Stewart
| | Moderator | | Join Date: Feb 2008 Location: Beauly, near Inverness, Scotland
Posts: 1,576
| | | re: Calendar year versus Fiscal year
By the way, I have used a slightly different convention for the year than you mention - in the UK financial years are usually quoted from the year they start, not the year they end, so 1 July 2008 would be Quarter 1 of fiscal year 2008/2009, hence the 2008 you see in the table above.
If you need to have this listed as 2009 the same function can be used - it is just a slight change that is needed, to line 9:
intYear = Year(somedate) + 1
and the test data then becomes - Date Quarter
-
01/07/2008 2009-Q1
-
01/08/2008 2009-Q1
-
01/09/2008 2009-Q1
-
01/10/2008 2009-Q2
-
01/11/2008 2009-Q2
-
01/12/2008 2009-Q2
-
01/01/2009 2009-Q3
-
01/02/2009 2009-Q3
-
01/03/2009 2009-Q3
-
01/04/2009 2009-Q4
-
01/05/2009 2009-Q4
-
01/06/2009 2009-Q4
-
01/07/2009 2010-Q1
-
01/08/2009 2010-Q1
-
01/09/2009 2010-Q1
-
01/10/2009 2010-Q2
-
01/11/2009 2010-Q2
-
01/12/2009 2010-Q2
-
01/01/2010 2010-Q3
-
01/02/2010 2010-Q3
-
01/03/2010 2010-Q3
-
01/04/2010 2010-Q4
-
01/05/2010 2010-Q4
-
01/06/2010 2010-Q4
-
01/07/2010 2011-Q1
-
01/08/2010 2011-Q1
-
01/09/2010 2011-Q1
-
01/10/2010 2011-Q2
-
01/11/2010 2011-Q2
-
01/12/2010 2011-Q2
-Stewart
| | Expert | | Join Date: Apr 2006 Location: Philadelphia
Posts: 5,218
| | | re: Calendar year versus Fiscal year
Quote:
Originally Posted by Stewart Ross Inverness By the way, I have used a slightly different convention for the year than you mention - in the UK financial years are usually quoted from the year they start, not the year they end, so 1 July 2008 would be Quarter 1 of fiscal year 2008/2009, hence the 2008 you see in the table above.
If you need to have this listed as 2009 the same function can be used - it is just a slight change that is needed, to line 9:
intYear = Year(somedate) + 1
and the test data then becomes
- Date Quarter
-
01/07/2008 2009-Q1
-
01/08/2008 2009-Q1
-
01/09/2008 2009-Q1
-
01/10/2008 2009-Q2
-
01/11/2008 2009-Q2
-
01/12/2008 2009-Q2
-
01/01/2009 2009-Q3
-
01/02/2009 2009-Q3
-
01/03/2009 2009-Q3
-
01/04/2009 2009-Q4
-
01/05/2009 2009-Q4
-
01/06/2009 2009-Q4
-
01/07/2009 2010-Q1
-
01/08/2009 2010-Q1
-
01/09/2009 2010-Q1
-
01/10/2009 2010-Q2
-
01/11/2009 2010-Q2
-
01/12/2009 2010-Q2
-
01/01/2010 2010-Q3
-
01/02/2010 2010-Q3
-
01/03/2010 2010-Q3
-
01/04/2010 2010-Q4
-
01/05/2010 2010-Q4
-
01/06/2010 2010-Q4
-
01/07/2010 2011-Q1
-
01/08/2010 2011-Q1
-
01/09/2010 2011-Q1
-
01/10/2010 2011-Q2
-
01/11/2010 2011-Q2
-
01/12/2010 2011-Q2
-Stewart Hello Stewart, always a pleasure to see you. I'm really confused over this whole 'Fiscal Year' definition. If you have time, can you kindly define the 4 Quarters for Fiscal Year 2009, as in: - Quarter 1 [2009] - 6/1/2008 to 9/30/2008, etc.
Thanks Stewart!
| | Moderator | | Join Date: Feb 2008 Location: Beauly, near Inverness, Scotland
Posts: 1,576
| | | re: Calendar year versus Fiscal year
Hi ADezii. In the financial community the fiscal year defines a specific tax period for accounting purposes. In the UK the norm for businesses is to have a financial year which runs from 1 April in one year to 31 March in the next year - but there are other patterns similar to the one mentioned by the original poster in post # 1. For example, as a matter of practicality Further Education colleges in Scotland changed their financial year patterns from 1 April year-start to 1 August to do away with the difficulty up to then of matching student activity running from August that straddled two financial years.
As today is 1 April, for many businesses in the UK financial year 2009/10 has just begun (or fiscal year 2010 in terms of the posts above). April, May and June 2009 form quarter 1 of 2009/10, July, August and September 2009 quarter 2, October, November and December 2009 quarter 3, and January, February and March 2010 form quarter 4 of 2009/10. The financial year/fiscal year is the accounting period for which businesses must by law publish audited accounts (at least in the UK). That period (whether referred to as 2009/10 or fiscal year 2010 or whatever else may be conventional) does not change just because the calendar year changes - so January, February and March of 2010 are simply referred to as quarter 4 of 2009/10.
In post 1 the poster refers to a fiscal year starting in quarter 3 (1 July each year), so the first half of the calendar year will belong to the previous fiscal year and the second the next, which is why in the exemplars listed in my final post you see the fiscal year part change in July. The fiscal year quarters for the poster's next fiscal year (2010 if I've correctly picked up the convention) run as follows:
2010 quarter 1: 07/01/2009 - 09/30/2009
2010 quarter 2: 10/01/2009 - 12/31/2009
2010 quarter 3: 01/01/2010 - 03/31/2010
2010 quarter 4: 04/01/2010 - 06/30/2010
Hope my imperfect explanation helps a little.
Regards
Stewart
| | Expert | | Join Date: Apr 2006 Location: Philadelphia
Posts: 5,218
| | | re: Calendar year versus Fiscal year
Quote:
Originally Posted by Stewart Ross Inverness Hi ADezii. In the financial community the fiscal year defines a specific tax period for accounting purposes. In the UK the norm for businesses is to have a financial year which runs from 1 April in one year to 31 March in the next year - but there are other patterns similar to the one mentioned by the original poster in post # 1. For example, as a matter of practicality Further Education colleges in Scotland changed their financial year patterns from 1 April year-start to 1 August to do away with the difficulty up to then of matching student activity running from August that straddled two financial years.
As today is 1 April, for many businesses in the UK financial year 2009/10 has just begun (or fiscal year 2010 in terms of the posts above). April, May and June 2009 form quarter 1 of 2009/10, July, August and September 2009 quarter 2, October, November and December 2009 quarter 3, and January, February and March 2010 form quarter 4 of 2009/10. The financial year/fiscal year is the accounting period for which businesses must by law publish audited accounts (at least in the UK). That period (whether referred to as 2009/10 or fiscal year 2010 or whatever else may be conventional) does not change just because the calendar year changes - so January, February and March of 2010 are simply referred to as quarter 4 of 2009/10.
In post 1 the poster refers to a fiscal year starting in quarter 3 (1 July each year), so the first half of the calendar year will belong to the previous fiscal year and the second the next, which is why in the exemplars listed in my final post you see the fiscal year part change in July. The fiscal year quarters for the poster's next fiscal year (2010 if I've correctly picked up the convention) run as follows:
2010 quarter 1: 07/01/2009 - 09/30/2009
2010 quarter 2: 10/01/2009 - 12/31/2009
2010 quarter 3: 01/01/2010 - 03/31/2010
2010 quarter 4: 04/01/2010 - 06/30/2010
Hope my imperfect explanation helps a little.
Regards
Stewart Explanation was spot on, thanks.
|  | Administrator | | Join Date: Oct 2006 Location: London - UK
Posts: 15,730
| | | re: Calendar year versus Fiscal year
I hope I haven't oversimplified, but wouldn't the following work for you? : - Format(DateAdd("m",7,Date()),"\qq yyyy")
Replace Date() with any date field you need to work with, and quotes (') for double-quotes (") when used within SQL to be technically correct.
| | Expert | | Join Date: Apr 2006 Location: Philadelphia
Posts: 5,218
| | | re: Calendar year versus Fiscal year
Quote:
Originally Posted by NeoPa I hope I haven't oversimplified, but wouldn't the following work for you? :
- Format(DateAdd("m",7,Date()),"\qq yyyy")
Replace Date() with any date field you need to work with, and quotes (') for double-quotes (") when used within SQL to be technically correct. You mean we did all that work for nothing? (LOL).
| | Familiar Sight | | Join Date: Aug 2006 Location: Grand Junction, CO
Posts: 233
| | | re: Calendar year versus Fiscal year
Quote:
Originally Posted by ADezii You mean we did all that work for nothing? (LOL). I doubt it was for nothing, at least this way I have multiple ways to try. I am having a long weekend from work so I won't be working on this till Monday, but I will let you know how it goes. Thanks for all the feedback, it all helps.
| | Administrator | | Join Date: Oct 2006 Location: London - UK
Posts: 15,730
| | | re: Calendar year versus Fiscal year
I certainly hope so ADezii :D
But in truth, I think RCollins has the right idea. All ideas are worth exploring.
| | Moderator | | Join Date: Feb 2008 Location: Beauly, near Inverness, Scotland
Posts: 1,576
| | | re: Calendar year versus Fiscal year
Not quite working, NeoPa (one quarter out in the changeovers), but it's a nice simplification. Applied to the same testdata as above: - Date Quarter Test
-
01/07/2008 2009-Q1 q1 2009
-
01/08/2008 2009-Q1 q1 2009
-
01/09/2008 2009-Q1 q2 2009
-
01/10/2008 2009-Q2 q2 2009
-
01/11/2008 2009-Q2 q2 2009
-
01/12/2008 2009-Q2 q3 2009
-
01/01/2009 2009-Q3 q3 2009
-
01/02/2009 2009-Q3 q3 2009
-
01/03/2009 2009-Q3 q4 2009
-
01/04/2009 2009-Q4 q4 2009
-
01/05/2009 2009-Q4 q4 2009
-
01/06/2009 2009-Q4 q1 2010
-
01/07/2009 2010-Q1 q1 2010
-
01/08/2009 2010-Q1 q1 2010
-
01/09/2009 2010-Q1 q2 2010
-
01/10/2009 2010-Q2 q2 2010
-
01/11/2009 2010-Q2 q2 2010
-
01/12/2009 2010-Q2 q3 2010
-
01/01/2010 2010-Q3 q3 2010
-
01/02/2010 2010-Q3 q3 2010
-
01/03/2010 2010-Q3 q4 2010
-
01/04/2010 2010-Q4 q4 2010
-
01/05/2010 2010-Q4 q4 2010
-
01/06/2010 2010-Q4 q1 2011
-
01/07/2010 2011-Q1 q1 2011
-
01/08/2010 2011-Q1 q1 2011
-
01/09/2010 2011-Q1 q2 2011
-
01/10/2010 2011-Q2 q2 2011
-
01/11/2010 2011-Q2 q2 2011
-
01/12/2010 2011-Q2 q3 2011
-Stewart
| | Moderator | | Join Date: Feb 2008 Location: Beauly, near Inverness, Scotland
Posts: 1,576
| | | re: Calendar year versus Fiscal year
...but with the following slight tweak - Format(DateAdd("m", 6, [somedate]), "yyyy-\Qq")
it gives... - Date Quarter Test
-
01/07/2008 2009-Q1 2009-Q1
-
01/08/2008 2009-Q1 2009-Q1
-
01/09/2008 2009-Q1 2009-Q1
-
01/10/2008 2009-Q2 2009-Q2
-
01/11/2008 2009-Q2 2009-Q2
-
01/12/2008 2009-Q2 2009-Q2
-
01/01/2009 2009-Q3 2009-Q3
-
01/02/2009 2009-Q3 2009-Q3
-
01/03/2009 2009-Q3 2009-Q3
-
01/04/2009 2009-Q4 2009-Q4
-
01/05/2009 2009-Q4 2009-Q4
-
01/06/2009 2009-Q4 2009-Q4
-
01/07/2009 2010-Q1 2010-Q1
-
01/08/2009 2010-Q1 2010-Q1
-
01/09/2009 2010-Q1 2010-Q1
-
01/10/2009 2010-Q2 2010-Q2
-
01/11/2009 2010-Q2 2010-Q2
-
01/12/2009 2010-Q2 2010-Q2
-
01/01/2010 2010-Q3 2010-Q3
-
01/02/2010 2010-Q3 2010-Q3
-
01/03/2010 2010-Q3 2010-Q3
-
01/04/2010 2010-Q4 2010-Q4
-
01/05/2010 2010-Q4 2010-Q4
-
01/06/2010 2010-Q4 2010-Q4
-
01/07/2010 2011-Q1 2011-Q1
-
01/08/2010 2011-Q1 2011-Q1
-
01/09/2010 2011-Q1 2011-Q1
-
01/10/2010 2011-Q2 2011-Q2
-
01/11/2010 2011-Q2 2011-Q2
-
01/12/2010 2011-Q2 2011-Q2
-Stewart
| | Administrator | | Join Date: Oct 2006 Location: London - UK
Posts: 15,730
| | | re: Calendar year versus Fiscal year
Quote:
Originally Posted by Stewart Ross Inverness Not quite working, NeoPa (one quarter out in the changeovers), but it's a nice simplification. You're right Stewart. I was sloppy with my arithmetic. I must admit that I was focusing my attention on the concept rather than the implementation, and rushed it out.
| | Moderator | | Join Date: Feb 2008 Location: Beauly, near Inverness, Scotland
Posts: 1,576
| | | re: Calendar year versus Fiscal year
Hi NeoPa. Your solution is a great example of using the built-in facilities (DateAdd and Format in this case) to the full before trying bespoke programming. It is commendably simple! I just wonder why I didn't think of it instead (shakes head...)
Cheers
Stewart
| | Administrator | | Join Date: Oct 2006 Location: London - UK
Posts: 15,730
| | | re: Calendar year versus Fiscal year
Thanks for that Stewart. I just wish I hadn't spoiled the effect with the nooby arithmetic ;)
| | Expert | | Join Date: Apr 2006 Location: Philadelphia
Posts: 5,218
| | | re: Calendar year versus Fiscal year
Quote:
Originally Posted by Stewart Ross Inverness Hi NeoPa. Your solution is a great example of using the built-in facilities (DateAdd and Format in this case) to the full before trying bespoke programming. It is commendably simple! I just wonder why I didn't think of it instead (shakes head...)
Cheers
Stewart Hey Stewart, I always said that NeoPa was commendably simple! I am referring to his programming skills, of course (LOL)!
| | Administrator | | Join Date: Oct 2006 Location: London - UK
Posts: 15,730
| | | re: Calendar year versus Fiscal year
Very nice ADezii. I actually laughed out loud on that one.
|  |
Similar Microsoft Access / VBA bytes | | |  /bytes/about
We are a network of experts and professionals in IT and software development that help one another with answers to tough questions and share insights.
Get the best answers to your questions from over 226,471 network members.
|
