Problem with Fiscal Quarters

[sk88]

I have this in Fiscal Quarters set up in one of my query. And I have another Crosstab query which will give me the answer to what I want to find.
For example, I wanted to know the amount of money spent in 2009 according to each quarter.
Given that Quarter 1 starts from April to June,
Quarter 2: July to September,
Quarter 3: October to December
and Quarter 4: January to April.


Now my problem is when I run that query for 2009 with respect to its Quarters, it gives me Quarter 4 of the previous year instead.
I wanted Quarter 4 of 2009 (which will be January to April of 2010).

Basically right now, Access took January to December of 2009.

Any idea how I should solve this problem?

What I have right now in one of my query column and under 'Field' is



Quarter: IIf(Month([Visit Date]) In (4,5,6),"1",IIf(Month([Visit Date]) In (7,8,9),"2",IIf(Month([Visit Date]) In (10,11,12),"3","4")))

[apr pillai]

Use the Choose() Function enveloped in a public function to find the Quarter value.

Public Function GetQrtr(ByVal Mnth as Integer) as Integer
    GetQrtr = Choose(Mnth,2,2,2,3,3,3,4,4,4,1,1,1)
End Function

Call the function with the Month Value of Date.

M = Month([Visit Date])
Qrtr = GetQrtr(M)

Query Colum usage:

Quarter:GetQrtr(Month([Visit Date]))

Find out how date and time values are stored internally from here.

[rpeare]

Add a Fiscal year calculation to your query

FY: iif(cint(datapart("m", [datefield]))>=7, datepart("yyyy", [datefield])+1, datepart("yyyy",[datefield]))

Then run your query for a specific FY

[sk88]

Add a Fiscal year calculation to your query

FY: iif(cint(datapart("m", [datefield]))>=7, datepart("yyyy", [datefield])+1, datepart("yyyy",[datefield]))

Then run your query for a specific FY

what do I put under 'datapart' and 'datafield'?

is 'datapart' my table name?
and 'datafield' my field name?

[rpeare]

That is a typo, this is very similar to the calculation in your other request

FY: iif(cint(datEpart("m", [datefield]))>=7, datepart("yyyy", [datefield])+1, datepart("yyyy",[datefield]))

[sk88]

That is a typo, this is very similar to the calculation in your other request

FY: iif(cint(datEpart("m", [datefield]))>=7, datepart("yyyy", [datefield])+1, datepart("yyyy",[datefield]))

YES! I finally made it! I made some changes to your code to something like this:

FY: IIf(CInt(DatePart("m",[VisitDate]))<=3,DatePart("yyyy",[VisitDate])-1,DatePart("yyyy",[VisitDate]))


I have attached an image here with my comment for you to see the output that I have right now and this is what I wanted.

THANK YOU SO MUCH rpeare! You've been of GREAT help!!