Fiscal year vs calendar year

  • Thread starter Thread starter Shawn
  • Start date Start date
S

Shawn

How do I create a report /query that will pull my data by
fiscal year instead of calendar year? I'm trying to build
a report that shows data by fiscal quarter and I'm running
into a roadblock. Please help!!
 
Limit the report's data to specific criteria. For example, if you want to
see the data for January only, your query would look something like this:

"SELECT * FROM YourTable WHERE YourDateField BETWEEN (#01/01/2003# AND
#01/30/2003#)"
 
Shawn said:
How do I create a report /query that will pull my data by
fiscal year instead of calendar year? I'm trying to build
a report that shows data by fiscal quarter and I'm running
into a roadblock. Please help!!
Click to expand...

How about:

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 (Using 7 for the first month):

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
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access
 
Back
Top