Calculating Fiscal Years

melika

Registered User.
Local time
Today, 17:04
Joined
Oct 29, 2007
Messages
16
Hi,

I have the following function:

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

I call on the function in my query to determine the FiscalYear for a date.
Expr1: GetFiscalYear([ProdDate])

It sends back the correct Fiscal Year. However, when I try to put criteria in for Expr1 to show only Fiscal Year 2007 instead of all fiscal years, it gives me a data mismatch type error.

The [ProdDate] has the following formula:
Prod_Date: IIf([40Day]="True",DateAdd("d",-40,[Date Code]),DateAdd("d",-50,[Date Code])) where [40Day] returns True or False to determine if I should subtract 40 or 50 days from the Date Code. The Date Code is in the format ##/##/#### and is sometimes null.

I have tried everything I could think of and am completely stumped. Thanks in advance for any help!!!
 
What is your criteria entry? Are you sure it's the same data type as is returned by your function? If your function is returning a Date/Time then you can't just use "2007" as a criteria because that's a number not a date.
 
Simple Software Solutions

Hi

Here is a function I use for detemining the fiscal year form a date

Public Function FinYear(AnyDate As Date) As String

Dim fYear As String
If Month(AnyDate) < 4 Then
FinYear = Year(DateAdd("yyyy", -1, AnyDate)) & "/" & Year(AnyDate)
Else
FinYear = Year(AnyDate) & "/" & Year(DateAdd("yyyy", 1, AnyDate))
End If


End Function


This example assumes that the first day of the financial year = 1st April

If you try the above using today's date you should get 2007/2008 returned


Code Master::cool:
 

Users who are viewing this thread

Back
Top Bottom