[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


Access 2007 Days served in a fiscal year

Posted on 2012-08-31
Medium Priority
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
  • 2
  • 2
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 38355316
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
ID: 38355337
...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, ...post 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 52

Accepted Solution

Gustav Brock earned 2000 total points
ID: 38355992
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

ID: 38365554
LVL 52

Expert Comment

by:Gustav Brock
ID: 38366717
You are welcome!


Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

873 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