# Thread: Report by Fiscal Year (Access 2K)

1. ## 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. ## 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. ## 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. ## 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. ## 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. ## 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. ## 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. ## Re: Report by Fiscal Year (Access 2K)

Thanks, Hans

It works! Thanks once again.

9. ## Re: Report by Fiscal Year (Access 2K)

Thanks, Hans

It works! I appreciate it.

10. ## 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. ## 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
•