selecting records since a certain date

[Jerry Crosby]

I have a table with a date field. I want to select all the records with a
date newer than the previous June 30 (July 1-June 30 is our fiscal year).
Because it spans calendar years, I can't just say, "go back as far as July
of this year." (e.g., if it was February 12, 2005, I'd want it to select
all records with a date newer than June 30, 2004).

How do I code the query to accomplish this?

Jerry

 

[Rick Brandt]

I have a table with a date field. I want to select all the records
with a date newer than the previous June 30 (July 1-June 30 is our
fiscal year). Because it spans calendar years, I can't just say, "go
back as far as July of this year." (e.g., if it was February 12,
2005, I'd want it to select all records with a date newer than June
30, 2004).

How do I code the query to accomplish this?

Jerry

....WHERE DateField > DateSerial(Year(Date()) - 1, 6, 30)

 

[Jerry Crosby]

Thanks Rick.

I don't have the database here at the office to test your suggestion on, but
as I look at it (with the little knowledge I have), it appears it might not
work.

Do I translate it to read, "where the year in the DateField is greater than
this year minus 1?" That would work in the example I gave, but if it's
November of our fiscal year and I run the report, I want it to get only
those dates newer than June 30 of that same year.

Will your suggestion do that?

Jerry

 

[Guest]

Jerry,

With just a little bit of modification, this should work

WHERE [DateField] > DateSerial(Year(Date()) - IIF(Month(Date()) > 6, 0, 1),
6, 30)

I added the IIF clause to test to see whether the current month is > 6, if
it is, then there is no need to decrement the year, if not, decrease the year
by one.

HTH
Dale

 

[Rick Brandt]

Thanks Rick.

I don't have the database here at the office to test your suggestion on, but
as I look at it (with the little knowledge I have), it appears it might not
work.

Do I translate it to read, "where the year in the DateField is greater than
this year minus 1?" That would work in the example I gave, but if it's
November of our fiscal year and I run the report, I want it to get only those
dates newer than June 30 of that same year.

Will your suggestion do that?

My suggestion broken down (using to today's date)...

WHERE DateField > DateSerial(Year(Date()) - 1, 6, 30)

WHERE DateField > DateSerial(Year(#7-21-2005#) - 1, 6, 30)

WHERE DateField > DateSerial(2005 - 1, 6, 30)

WHERE DateField > DateSerial(2004, 6, 30)

WHERE DateField > #6-30-2004#

Basically, the DateSerial returns June 30 of the previous year regardless of
what the current date is.