Date Limit Criteria - January of Previous Fiscal Year

[CMA]

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?

 

[Marshall Barton]

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)

 

[John Spencer]

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]

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]

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.