Results 1 to 11 of 11
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Sunrise, Florida
    Posts
    324
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Report by Fiscal Year (Access 2K)

    I give up! To no avail, I've been trying to create a report that will group investigation's records by the Fiscal Year (beginning on 7/1) the investigation was opened (CreationDate), will calculate the number of days it took to close the file (CreationDate - ClosingDate) and present the average of all the total days (CreationDate - ClosingDate) for that Fiscal Year. I got the following code from Microsoft but don't know how to apply it to my problem.
    <----------Code---------->
    Option Explicit

    Const FMonthStart = 7 ' Numeric value representing the first month
    ' of the fiscal year.
    Const FDayStart = 1 ' Numeric value representing the first day of
    ' the fiscal year.
    Const FYearOffset = -1 ' 0 means the fiscal year starts in the
    ' current calendar year.
    ' -1 means the fiscal year starts in the
    ' previous calendar year.

    Function GetFiscalYear(ByVal x As Variant)
    If x < DateSerial(Year(x), FMonthStart, FDayStart) Then
    GetFiscalYear = Year(x) - FYearOffset - 1
    Else
    GetFiscalYear = Year(x) - FYearOffset
    End If
    End Function

    Function GetFiscalMonth(ByVal x As Variant)
    Dim m
    m = Month(x) - FMonthStart + 1
    If Day(x) < FDayStart Then m = m - 1
    If m < 1 Then m = m + 12
    GetFiscalMonth = m
    End Function
    <----------Code---------->

    Any help is appreciated. Thanks in advance!!!

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Report by Fiscal Year (Access 2K)

    Put the code from Microsoft in a standard module.
    Create a query based on your table.
    Add a calculated column:

    FiscalYear: GetFiscalYear([CreationDate])

    and add another calculated column:

    Duration: [ClosingDate] - [CreationDate]

    Save this query, and use it as record source for your report. Group the report by FiscalYear, and in the group footer, put a text box with control source

    =Avg([Duration])

    Does this give you enough to start with?

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Sunrise, Florida
    Posts
    324
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report by Fiscal Year (Access 2K)

    Are you kidding me? Not only does it give me enough to start with; it saved me typing milion of characters to get nowhere! Thanks a million and Happy Holidays!

  4. #4
    New Lounger
    Join Date
    Jun 2008
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report by Fiscal Year (Access 2K)

    This looks a lot like what I need for a report. But how can I specify which Fiscal Year to show the results for. For example, when I try to put 2009 in the criteria for FiscalYear, the module returns an invalid use of null error because "x" is being passed as null.

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Report by Fiscal Year (Access 2K)

    If you change the function to
    <code>
    Function GetFiscalYear(ByVal x As Variant)
    If IsNull(x) Then
    GetFiscalYear = Null
    ElseIf x < DateSerial(Year(x), FMonthStart, FDayStart) Then
    GetFiscalYear = Year(x) - FYearOffset - 1
    Else
    GetFiscalYear = Year(x) - FYearOffset
    End If
    End Function
    </code>
    it will handle Null values too.

  6. #6
    2 Star Lounger
    Join Date
    Jan 2005
    Location
    Wichita, Kansas, USA
    Posts
    209
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report by Fiscal Year (Access 2K)

    I have what I think is a variation on this theme. I have a series of reports that are repeated every fiscal year that starts in July (Fiscal year 2009 starts on July 1, 2008). I would like to have these reports printed each year such that the headers would display "2009" automatically, rather than having to edit the header manually every year. How might I do this?

    Warren

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Report by Fiscal Year (Access 2K)

    Copy the code to a standard module.

    Place a text box in the report header with the following Control Source:

    =GetFiscalYear(Date())

  8. #8
    2 Star Lounger
    Join Date
    Jan 2005
    Location
    Wichita, Kansas, USA
    Posts
    209
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report by Fiscal Year (Access 2K)

    Thanks, Hans

    It works! Thanks once again.

  9. #9
    2 Star Lounger
    Join Date
    Jan 2005
    Location
    Wichita, Kansas, USA
    Posts
    209
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report by Fiscal Year (Access 2K)

    Thanks, Hans

    It works! I appreciate it.

  10. #10
    2 Star Lounger
    Join Date
    Jan 2005
    Location
    Wichita, Kansas, USA
    Posts
    209
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report by Fiscal Year (Access 2K)

    Thanks, Hans

    It works! I appreciate it.

    Is there a way to set a default date in a table to be "The second Saturday in May for the current fiscal year"? Or, to run an update query at the start of the next fiscal year to change all of my annual dates automatically, so that, for example, "Spring Meeting" (which is on the second Saturday of March every year) would change from May 10, 2008 in the table to May 9, 2009 ?

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Report by Fiscal Year (Access 2K)

    You need another custom function to calculate the 2nd Saturday in May in a given year, but you can't use a custom function in the Default Value property of a field. So you cannot do this in a table. But you can use the custom function in a query and in a form. Here it is:

    ' Will return the first, second, third, fourth, fifth or last day of type aDay
    ' in the given year and month.

    ' aYear is the year
    ' aMonth is the month
    ' aDay can be 1=Sunday ... 7=Saturday
    ' aNum can be 1, 2, 3, 4, 5 or 9=Last

    ' Examples:
    ' DayInMonth(2002, 5, 6, 3) will return the 3rd Friday in May, 2002
    ' DayInMonth(2002, 12, 3, 9) will return the last Tuesday in December, 2002

    Function DayInMonth(aYear As Long, aMonth As Long, aDay As Long, aNum As Long) As Date
    If aNum = 9 Then
    DayInMonth = DayInMonth(aYear, aMonth + 1, aDay, 1) - 7
    Else
    DayInMonth = DateSerial(aYear, aMonth, _
    7 * aNum + 1 - WeekDay(DateSerial(aYear, aMonth, 1), aDay Mod 7 + 1))
    End If
    End Function

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •