calculating quarters

kim40

Registered User.
Local time
Today, 06:59
Joined
Aug 24, 2012
Messages
30
Code:
 Quarter: DatePart("q",DateAdd("m",6,[Date Paid]))
I have this code that calculates the date in quarters. My financial year is july to Dec. It works fine except that it sees june 2012 in the fouth quarter.
How do I modify this?
 
You posted a lot of confusing information. July to Dec is 6 months, not a year. So does that mean your quarters are 1.5 months? If not, then it sounds like June should be Q4. If so, then June shouldn't be in your data at all.
 
sorry meant july to june. I agree June should be in the 4th quater but not in the current financial period. the financial period is july 2012 to june 2013. but june 2012 is in the 4th quarter of the 2012-2013 year
hope this is clearer
thanks
 
It explains a little, but doesn't tell me what should be returned. Your code:

Code:
Quarter: DatePart("q",DateAdd("m",6,[Date Paid]))

only returns a quarter, it doesn't return a year. What would you like returned when the [Date Paid] is 12/15/2011?
 
I would like returned quarter and financial year (e.g june 2012 would be 4th quarter 2011).
 
So you need a field
Foryear: iif(Quarter>2,year(date())-1,year(date()))

Brian
 
This query will give you what you want (be sure to replace 'YourTableNameHere' with the name of your actual table):

Code:
SELECT DatePart("q",DateAdd("m",6,[Date Paid])) AS FiscalQuarter, Year(DateAdd("m",6,[Date Paid])) - IIF(Month(DateAdd("m",6,[Date Paid]))<7, 1,0) AS FiscalYear, [Date Paid]
FROM YourTableNameHere;
 

Users who are viewing this thread

Back
Top Bottom