fiscal year

[tvillare]

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.

 

[George Nicholson]

Add, don't subtract.

Fiscal Year: Year([date])+(Month([date])>6)

So that anything between July 1, 2007 and June 30, 2008 would be labeled as
Fiscal Year 2008.

 

[KARL DEWEY]

Based on your fiscal year, whether it starts late or begins early use ---
Starts late try this --
DatePart("yyyy",DateAdd("m",-6,[YourDate]))
15 July 2007 being first month of FY 2007
or
Starts early ---
DatePart("yyyy",DateAdd("m",6,[YourDate]))
15 July 2007 being first month of FY 2008

--
KARL DEWEY
Build a little - Test a little

Add, don't subtract.

Fiscal Year: Year([date])+(Month([date])>6)

So that anything between July 1, 2007 and June 30, 2008 would be labeled as
Fiscal Year 2008.

--
HTH,
George

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.

 

[Douglas J. Steele]

Try using DateAdd("m", 6, [date])

Hopefully your table field isn't actually named Date. If it is, rename it:
date is a reserved word, and should never be used for your own purposes. For
a good discussion of names to avoid, see what Allen Browne has at
http://www.allenbrowne.com/AppIssueBadWord.html

 

[Arvin Meyer [MVP]]

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]

 

[jtwin70]

I'm having a similar problem - building an expression in a query based on a
date field. I have the transaction dates in a "MMDDYYYY"-formatted field,
and want to create a new field using the expression to assign a Fiscal Year
based on date. Is there an IIF expression that I could use that will
accomplish this?
--
Jeff S.

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

 

[John W. Vinson]

I'm having a similar problem - building an expression in a query based on a
date field. I have the transaction dates in a "MMDDYYYY"-formatted field,
and want to create a new field using the expression to assign a Fiscal Year
based on date. Is there an IIF expression that I could use that will
accomplish this?

The format of the field (if it IS a date/time field) is completely irrelevant.
If your FY starts on July 1, so that 7/1/2007 is the first day of FY2008, you
can calculate the fiscal year by simply adding six months to the transaction
date:

Year(DateAdd("m", 6, [TransactionDate]))


John W. Vinson [MVP]