Results 1 to 3 of 3
  1. #1
    blazerboy6 is offline Trained Beginner
    Windows 7 32bit Access 2007
    Join Date
    Apr 2011
    Location
    Portland, OR
    Posts
    28

    July-June Fiscal Year, Not Jan-Dec

    I'm having trouble getting Access 2007 to return a formatted qry/rpt for our fiscal quarter. The report runs fine, except we have to know that Q1 2011 on the report actually means our own Q3 2010/2011.

    The current expression in the qry is this:

    HTML Code:
    rmDate By Quarter: Format$([tblReportingMatrix].[rmDate],'\Qq yyyy')
    Do I need to write a complex SQL statement or something of that nature, to define Q1 as between July 1 and Sept 30, etc? Is there a simpler way? Help!

  2. #2
    ajetrumpet is offline VIP
    Windows Vista Access 2007
    Join Date
    Mar 2010
    Location
    N/A
    Posts
    2,694
    July 1 - Sept 30 is quarter #3 to computers, so why not just use a DATEADD() function and use the "q" parameter in the form timescale argument?? You could put an IIF() statement in it as well, just in case the quarter the computer is on is > 2. If so, it needs to circle back to 1 or 2 for you.

  3. #3
    blazerboy6 is offline Trained Beginner
    Windows 7 32bit Access 2007
    Join Date
    Apr 2011
    Location
    Portland, OR
    Posts
    28
    ajetrumpet,

    Thanks for the recommendation of DateAdd, I didn't have to worry about an iif statement since I wanted the first two quarters to group with the next calendar year. Here's what I came up with:

    Code:
    rmDate By Quarter: Format$(DateAdd("q",2,[tblReportingMatrix].[rmDate]),'\Qq') 
    & " " & Format$(DateAdd("q",-2,[tblReportingMatrix].[rmDate]),'yyyy') 
    & "-" & Format$(DateAdd("q",2,[tblReportingMatrix].[rmDate]),'yyyy')
    This now gives me the correct result of:
    'Q1 2011-2012' for a date of '7/12/2011' instead of 'Q3 2011'.

    Thanks for your help.

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

Similar Threads

  1. Year to date sum
    By jzacharias in forum Database Design
    Replies: 6
    Last Post: 09-10-2010, 10:38 AM
  2. fiscal year
    By RedGoneWILD in forum Programming
    Replies: 4
    Last Post: 08-04-2010, 01:38 PM
  3. Replies: 2
    Last Post: 06-30-2010, 12:26 PM
  4. by year by month
    By nkuebelbeck in forum Reports
    Replies: 21
    Last Post: 03-24-2010, 01:53 PM
  5. Use field value as the year in a date
    By Eveline in forum Queries
    Replies: 1
    Last Post: 03-11-2010, 10:58 AM

Tags for this Thread

Posting Permissions

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