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?
19  6267  @rcollins
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:
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
@rcollins
Where to I keep the function?
In a Standard Code Module
Do I do this in a module?
Previously answered
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
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.
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
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
@Stewart Ross Inverness
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!
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
NeoPa 32,556
Expert Mod 16PB
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.
@NeoPa
You mean we did all that work for nothing? (LOL).
@ADezii
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.
NeoPa 32,556
Expert Mod 16PB
I certainly hope so ADezii :D
But in truth, I think RCollins has the right idea. All ideas are worth exploring.
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
...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
NeoPa 32,556
Expert Mod 16PB @Stewart Ross Inverness
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.
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
NeoPa 32,556
Expert Mod 16PB
Thanks for that Stewart. I just wish I hadn't spoiled the effect with the nooby arithmetic ;)
@Stewart Ross Inverness
Hey Stewart, I always said that NeoPa was commendably simple! I am referring to his programming skills, of course (LOL)!
NeoPa 32,556
Expert Mod 16PB
Very nice ADezii. I actually laughed out loud on that one.
Sign in to post your reply or Sign up for a free account.
Similar topics
by: bp |
last post by:
I'm using the System.Globalization.GregorianCalendar class to calculate
week numbers. My weeks begin on Saturday and follow the "first four
day" rule.
GregorianCalendar cal = new...
|
by: charliewest |
last post by:
Can someone recommend a fairly easy to use ASP.NET custom calendar control
that makes it easy for the user to select the month and year - versus the
inherent "paging" that is used in the default...
|
by: ajmera.puneet |
last post by:
If I have Calendar Control on Asp.net page and I have a table for
Fiscal years on sql server
then, How can I check the dates from table to Calendar Control,so that
I can format the Calendar...
|
by: rkohon |
last post by:
Hello all,
I am new to JavaScript and need some ideas, suggestions, or code snippets.
I have a form which requires the end user to put in a date for required items. I need javascript function to...
|
by: Twobridge |
last post by:
Hi
I am trying to perform a search that will return records based on a
fiscal year search of the bill_Date. The user gives the year then I
want to search based on the fiscal year (July 1 - June...
|
by: Sund via AccessMonster.com |
last post by:
I do fair amount of data analysis using access pivot tables and charts. Can
any body suggest a method to run the queries based on accounting month and
Accounting year.As an example: I want to...
|
by: craigfr |
last post by:
I am making a graph comparing last year's defect data with YTD defect data. Our fiscal year starts Nov.1 and ends Oct.31.
To get the YTD, I started used a simple date serial criteria:
Between...
|
by: Lars Eighner |
last post by:
Is a calendar tabular data, logically meriting table markup?
--
Lars Eighner <http://larseighner.com/ <http://myspace.com/larseighner>
Countdown: 465 days to go.
What do you do when...
|
by: mathewgk80 |
last post by:
HI all,
I am having popup calendar Javascript code. But i dont know how it is connecting to asp.net code.. I am using asp.net,c#.net and also using 3tier architecture with master page....
I...
|
by: abhishekbrave |
last post by:
The code below is opening a calendar on mouse over in the same window.
I need the calendar to be opened in new window.
Have to fulfill this requirement urgentely so posting the whole code here.
I...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome former...
|
by: ryjfgjl |
last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
| |
