Results 1 to 6 of 6

Problem with Fiscal Quarters

  1. #1
    sk88 is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Aug 2011
    Posts
    153

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

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

  2. #2
    apr pillai's Avatar
    apr pillai is offline Contributor
    Windows 7 64bit Access 2007
    Join Date
    May 2010
    Location
    Alappuzha, India
    Posts
    134
    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. #3
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    3,788
    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

  4. #4
    sk88 is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Aug 2011
    Posts
    153
    Quote Originally Posted by rpeare View Post
    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?

  5. #5
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    3,788
    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. #6
    sk88 is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Aug 2011
    Posts
    153
    Quote Originally Posted by rpeare View Post
    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!!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 14
    Last Post: 12-06-2012, 11:12 AM
  2. Fiscal Year in Access 2007
    By blindhawkeye in forum Access
    Replies: 1
    Last Post: 08-16-2011, 01:38 PM
  3. July-June Fiscal Year, Not Jan-Dec
    By blazerboy6 in forum Access
    Replies: 2
    Last Post: 04-14-2011, 01:23 PM
  4. fiscal year
    By RedGoneWILD in forum Programming
    Replies: 4
    Last Post: 08-04-2010, 12:38 PM
  5. Replies: 2
    Last Post: 03-31-2010, 04:32 AM

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