tvillare said:
I have created an expression to determine fiscal year, but it gives me the
wrong information. Our fiscal year starts July 1st and ends June 30th.
Here
is the expressions: Fiscal Year: Year([date])-(Month([date])>6). I'm
using
month and year.
Here are 2 functions I wrote almost 10 years ago:
This is a function for non-calendar fiscal years.
intFMonth = the First Month of the Fiscal Year
Function GetFY(dtmDate As Date, intFMonth As Integer) As String
' ©Arvin Meyer 9/27/1998
On Error Resume Next
Dim intMonth As Integer
Dim intYear As Integer
intMonth = Month(dtmDate)
intYear = Year(dtmDate)
If intMonth >= intFMonth Then intYear = intYear + 1
GetFY = "FY" & str(intYear)
End Function
==================
Here is some code for fiscal year calculation, and how you'd use it in a
query:
intFMonth = the First Month of the Fiscal Year
in your case 7 for July
Function FY(dtDateIn As Date, intFMonth As Integer) As String
' intFMonth = the First Month of the Fiscal Year
' ©Arvin Meyer 9/27/1997
On Error Resume Next
Dim intMonth As Integer
Dim intYear As Integer
intMonth = Month(dtDateIn)
intYear = Year(dtDateIn)
If intMonth >= intFMonth Then intYear = intYear + 1
FY = str(intYear)
End Function
The YearToDate would be:
Select DateField From Table1
Where FY([DateField],7)=FY(Date(),7);
A calculation for a full year would depend upon when you ran the query. You
would use:
Between FY([DateField],7) And [The ending date or an expression]
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com