Query based on calendar year.

[Guest]

Hi!

I’ve a question about selecting records from a table into another table.
I’ve 2 tables, T_OE_Ideas and T_OE_Ideas_Queried.

What I want is a query that copies 2 calendar years (current year and last
year) into the table T_OE_Ideas_Queried. Our calendar year is from April till
Februari.

At this moment, I have a Query that selects the last 24 months but this
isn’t correct given that I need the selection by calendar year. This is my
current query:

 

[John Spencer]

I question why you are copying data from one table into another. Why not
just use a SELECT query as the source for whatever you are doing?

What happened to March?
SELECT * FROM T_OE_Ideas
WHERE T_OE_Ideas.Fld_Date Between DateSerial(Year(Date())-2,4,1) And
DateSerial(Year(Date()),3,0)

If you ran that right now Feb 26, 2007 , you would get records from
April 1, 2005 to February 28, 2007

You may have to make adjustments to the above criteria, depending on what
you want to happen in say July of this year. Do you want
April 2005 to Feb 2007 or do you want April 2006 to Feb 2008? If the
latter, you can adjust the year by using something like

DateSerial(Year(Date()) -1 + Month(Date())<=4,4,1) and
DateSerial(Year(Date()) -1 + Month(Date())<=4,3,0)

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

 

[Guest]

Hi John thanks for your reply!

First of all, indeed.. we do have March but I totally forgot to mention! Our
calendar year is from April till March. The reason why I copy the selection
into another table, is because of the export later on. I use this second
table for a “DoCmd.TransferSpreadsheet†command to Excel.

What I want is a selection of the current book year and our last book year,
e.g. at this moment (Feb. 26 07): April 2005 - March 2007. In April 2007 the
selection must be: April 2006 – March 2008.

Is this possible?

Stefan van der Hooft.

 

[John Spencer]

Try playing around with the criteria. I think you would want the following

Between DateSerial(Year(Date()) -1 + (Month(Date())<=3),4,1)
and DateSerial(Year(Date()) +1 + (Month(Date())<=3),4,0)

That returns
Between 4/1/2005 and 3/31/2007 when the date is Feb 26, 2007

Between 4/1/2006 and 3/31/2008 when the date is April 1, 2007


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