# Problem with Fiscal Quarters

1. Competent Performer
Windows XP Access 2007
Join Date
Aug 2011
Posts
179

## Problem with Fiscal Quarters

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).

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")))

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

Code:
```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.

3. VIP
Windows XP Access 2003
Join Date
Jul 2011
Posts
4,464

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

Then run your query for a specific FY

4. Competent Performer
Windows XP Access 2007
Join Date
Aug 2011
Posts
179
Originally Posted by rpeare

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?

5. VIP
Windows XP Access 2003
Join Date
Jul 2011
Posts
4,464
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]))

6. Competent Performer
Windows XP Access 2007
Join Date
Aug 2011
Posts
179
Originally Posted by 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]))

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!!

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•
Tech Forums: Microsoft Office Forums