Date Limit Criteria - January of Previous Fiscal Year

I am trying to set a "dynamic" criteria limit for a date field that will
limit query results to everything occuring since the January BEFORE the
current fiscal year. Our Fiscal year is April 1 to March 31.

If today's date is November 28, 2007, I want to see all results occuring
from January 1, 2007 to today.

If today's date is March 15, 2008, I want to see all results from Jan 1 2007
to March 15, 2007

If today is April 2, 2008, I want to see only results from Jan 1 2008 to
April 2, 2008.

What formula can I use to accomplish this?

CMA wrote:

I am trying to set a "dynamic" criteria limit for a date field that will
limit query results to everything occuring since the January BEFORE the
current fiscal year. Our Fiscal year is April 1 to March 31.

If today's date is November 28, 2007, I want to see all results occuring
from January 1, 2007 to today.

If today's date is March 15, 2008, I want to see all results from Jan 1 2007
to March 15, 2007

If today is April 2, 2008, I want to see only results from Jan 1 2008 to
April 2, 2008.

What formula can I use to accomplish this?

DateSerial(Year(DateAdd("m", -3,Date())), 1, 1)

--
Marsh
MVP [MS Access]

I think what you want is something like the following

Between DateSerial(Year(Date()) + Month(Date()) 4,1,1) and Date()

Although that fails on your second example since it would report data for
January 2007 to March 15, 2008 versus March 15, 2007.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

"CMA" wrote in message
...
I am trying to set a "dynamic" criteria limit for a date field that will
limit query results to everything occuring since the January BEFORE the
current fiscal year. Our Fiscal year is April 1 to March 31.

If today's date is November 28, 2007, I want to see all results occuring
from January 1, 2007 to today.

If today's date is March 15, 2008, I want to see all results from Jan 1
2007
to March 15, 2007

If today is April 2, 2008, I want to see only results from Jan 1 2008 to
April 2, 2008.

What formula can I use to accomplish this?

Thanks,

It does appear to work (though on my work PC, I cannot change the current
system date to test future dates). Can you (or anyone else) direct me to a
resource where I can learn to understand the provided formula?

"Marshall Barton" wrote:

CMA wrote:

I am trying to set a "dynamic" criteria limit for a date field that will
limit query results to everything occuring since the January BEFORE the
current fiscal year. Our Fiscal year is April 1 to March 31.

If today's date is November 28, 2007, I want to see all results occuring
from January 1, 2007 to today.

If today's date is March 15, 2008, I want to see all results from Jan 1 2007
to March 15, 2007

If today is April 2, 2008, I want to see only results from Jan 1 2008 to
April 2, 2008.

What formula can I use to accomplish this?

DateSerial(Year(DateAdd("m", -3,Date())), 1, 1)

--
Marsh
MVP [MS Access]

CMA wrote:
It does appear to work (though on my work PC, I cannot change the current
system date to test future dates). Can you (or anyone else) direct me to a
resource where I can learn to understand the provided formula?


Check each function in VBA Help.

Hit Ctrl+g and you can test the expression in the
Immediate/Debug window.

It is not necessary to change your system date. Just set a
variable to whatever date you want and use the variable in
place of the Date() function.

mydate = #3/31/2008#
?DateSerial(Year(DateAdd("m", -3, mydate )), 1, 1)

Change mydate to any valid date and hit Enter twice to see
the reult.

--
Marsh
MVP [MS Access]