select dates for last fiscal year

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Our fiscal year runs April 1 - March 31. I have a query whose date criteria
selects all dates during the current fiscal year (code below) and it's
working fine.

Between DateSerial(DatePart("yyyy",DateAdd("m",-3,Date())),4,1) And
DateSerial(DatePart("yyyy",DateAdd("m",+9,Date())),3,31)

However, now that we're into a new fiscal year I need a report that selects
all the dates from the previous fiscal year. If it was run today, that would
be all dates from April 1, 2006 through March 31, 2007. I should never need
to go back more than one previous fiscal year and I wouldn't be running this
report after December 31 of this year (in which case the previous fiscal year
would begin two years prior). It's just that during the opening days of the
new fiscal year I want to be able to run some year-end reports.

So, how would I tweak the above formula to get me what I need?

Thanks in advance.

Jerry
 
For Prior FY
Between DateSerial(Year(Date())-1, 4,1) And DateSerial(Year(Date()),3,31)

For Current FY
Between DateSerial(Year(Date()), 4,1) And DateSerial(Year(Date())+1,3,31)

Switch between prior or current FY based on a current month being Between 4
and 6

Between DateSerial(Year(Date()) - IIF(Month(Date()) >3 and Month(Date()) <
7,1,0), 4,1)
And DateSerial(Year(Date())+1 - IIF(Month(Date()) >3 and Month(Date()) <
7,1,0),3,31)

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
Try this --
DatePart("yyyy",DateAdd("m",-3,[YourDate]))
Use this as criteria --
[Enter FY (2002)]
 
Back
Top