Access 2007 Days served in a fiscal year

Posted on 2012-08-31
Last Modified: 2012-09-04
A lot of my data mutilation involves dates and duration around services that a client receives. My issue is that when a client services cross over into different fiscal years.  I need to show how many days client received services in each fiscal year.  

Example of fiscal year:  FY13:  7/1/2012 - 6/30/2013
                                      FY12: 7/1/2011 - 6/30/2012

example of a client date of service: 5/14/2012 - 8/14/2012

Total days of service is 91

I need to break the days of service in FY that was served.

Days in FY12 should be : 47

Days in FY13 should be : 44
Question by:jbakestull
    LVL 74

    Expert Comment

    by:Jeffrey Coachman
    As a start, you should have a "FiscalYears table:

    FiscalYearID (Primary Key)
    FiscalYearStartDate (Date/Time)
    FiscalYearEndDate (Date/Time)
    FiscalYearDays (number) (possibly optional if the days are "calculated")

    You will have to post more info about how the service daya are determined though...
    ...and also tell us about any invalid date or date ranges...

    In other words, post an example of some data (for all contingencies) and the exact results you are looking for, based on the sample data.

    LVL 74

    Expert Comment

    by:Jeffrey Coachman
    ...or better yet, just post a sample of this database...

    Sample database notes:
    1. Back up your database(s).
    2. Combine the front and back ends into one database file.
    3. Remove any startup options, unless they are relevant to the issue.
    4. Remove any records unless they are relevant to the issue.
    5. Delete any objects that do not relate directly to the issue.
    6. Remove any references to any "linked" files (files outside of the database, Images, OLE Files, ...etc)
    7. Remove any references to any third party Active-x Controls (unless they are relevant to the issue)
    8. Remove, obfuscate, encrypt, or otherwise disguise, any sensitive data.
    9. Unhide and hidden database objects
    10. Compile the code. (From the VBA code window, click: Debug-->Compile)
    11. Run the compact/Repair utility.
    12. Remove any Passwords and/or security.
    13. If a form is involved in the issue, set the Modal and Popup properties to: No
        (Again, unless these properties are associated with the issue)
    14. Post the explicit steps to replicate the issue.
    15. Test the database before posting.

    In other words, a database that we can easily open and immediately see and/or troubleshoot the issue.
    And if applicable, also include a clear graphical representation of the *Exact* results you are expecting, based on the sample data.

    LVL 48

    Accepted Solution

    You can use these expressions:

    ? DateDiff("d", #5/14/2012#, #8/14/2012#)

    ? DateDiff("d", #5/14/2012#, DateFinancialFirst)

    ? DateDiff("d", DateFinancialFirst, #8/14/2012#)

    Public Function DateFinancialFirst() As Date
      Dim datFin    As Date
      Dim datNow    As Date
      Dim datFirst  As Date
      datNow = Date
      datFin = DateFinancial(datNow)
      datFirst = DateSerial(Year(datFin), 1 - DateDiff("m", datNow, datFin), 1)
      DateFinancialFirst = datFirst
    End Function
    Public Function DateFinancial( _
      ByVal datDate As Date) _
      As Date
      ' Number of months from start of calendar year to start of financial year.
      Const clngMonthOffset As Long = 6
      Dim datFinancial      As Date
      datFinancial = DateAdd("m", -clngMonthOffset, datDate)
      DateFinancial = datFinancial
    End Function

    Open in new window


    Author Closing Comment

    LVL 48

    Expert Comment

    by:Gustav Brock
    You are welcome!


    Featured Post

    Highfive Gives IT Their Time Back

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
    A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
    This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    13 Experts available now in Live!

    Get 1:1 Help Now