select dates for last fiscal year

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
 
J

John Spencer

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
..
 
G

Guest

Try this --
DatePart("yyyy",DateAdd("m",-3,[YourDate]))
Use this as criteria --
[Enter FY (2002)]
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top