Complex date query, Suggestions please

[Robert]

I need to set up a query that will pick out records for the current winter
season.

I.e., each season runs from October 1 until March 31. The catch is, the year
can't be hard coded. So, if the query is run on September 30 of this year
(2006) it should show all records with a date between October 1, 2005 and
March 31, 2006. If that same query is run on October 1, 2006 it should show
all records with a date from October 1, 2006 through March 31, 2007. This
would then perpetuate each year. Any suggestions on where to begin?

[CDMAPoster]

Robert wrote:

I need to set up a query that will pick out records for the current winter
season.

I.e., each season runs from October 1 until March 31. The catch is, the year
can't be hard coded. So, if the query is run on September 30 of this year
(2006) it should show all records with a date between October 1, 2005 and
March 31, 2006. If that same query is run on October 1, 2006 it should show
all records with a date from October 1, 2006 through March 31, 2007. This
would then perpetuate each year. Any suggestions on where to begin?

I would use something like:

SELECT * FROM MyTable WHERE DateValue(TimeStamp) BETWEEN
DateSerial(Year(Date()) + Int(Format(Date(), "yyyy.mmdd") -
Format(DateSerial(Year(Date()), 10, 1), "yyyy.mmdd")), 10, 1) AND
DateSerial(Year(Date()) + Int(Format(Date(), "yyyy.mmdd") -
Format(DateSerial(Year(Date()), 10, 1), "yyyy.mmdd")) + 1, 3, 31);

James A. Fortune
CD********@FortuneJames.com

[Jimmy]

<CD********@FortuneJames.comwrote in message

>
I would use something like:

SELECT * FROM MyTable WHERE DateValue(TimeStamp) BETWEEN

What is TimeStamp? Access didn't recognize it as a built in function so I'm
assuming it refers to the current date, thus using Date() in its place would
accomplish the same thing?

Also, when I used the query as written (replacing TimeStamp with Date() )
the results include all entries in the table.

DateSerial(Year(Date()) + Int(Format(Date(), "yyyy.mmdd") -
Format(DateSerial(Year(Date()), 10, 1), "yyyy.mmdd")), 10, 1) AND
DateSerial(Year(Date()) + Int(Format(Date(), "yyyy.mmdd") -
Format(DateSerial(Year(Date()), 10, 1), "yyyy.mmdd")) + 1, 3, 31);

James A. Fortune
CD********@FortuneJames.com

[CDMAPoster]

Jimmy wrote:

<CD********@FortuneJames.comwrote in message

I would use something like:

SELECT * FROM MyTable WHERE DateValue(TimeStamp) BETWEEN

What is TimeStamp? Access didn't recognize it as a built in function so I'm
assuming it refers to the current date, thus using Date() in its place would
accomplish the same thing?

Also, when I used the query as written (replacing TimeStamp with Date() )
the results include all entries in the table.

DateSerial(Year(Date()) + Int(Format(Date(), "yyyy.mmdd") -
Format(DateSerial(Year(Date()), 10, 1), "yyyy.mmdd")), 10, 1) AND
DateSerial(Year(Date()) + Int(Format(Date(), "yyyy.mmdd") -
Format(DateSerial(Year(Date()), 10, 1), "yyyy.mmdd")) + 1, 3, 31);

James A. Fortune
CD********@FortuneJames.com

You said:

"it should show all records with a date between October 1, 2005 and
March 31, 2006"

You didn't indicate the field name you use for this date so I used a
field called TimeStamp in place of your field.

James A. Fortune
CD********@FortuneJames.com