Return a date range based on today's date

On Tue, 1 Dec 2009 08:08:01 -0800, BABs
wrote:

I need to build a query (to be used for eternity) to return records for dates
between 01 July (the year prior) and 30 Jun (the current year) based on the
date when the query is run.
Is this possible?

Not just possible but easy:

= DateSerial(Year(Date()) - 1, 7, 1) AND DateSerial(Year(Date()), 7, 1)

--

John W. Vinson [MVP]

On Tue, 1 Dec 2009 08:08:01 -0800, BABs
wrote:

I need to build a query (to be used for eternity) to return records for dates
between 01 July (the year prior) and 30 Jun (the current year) based on the
date when the query is run.
Is this possible?

Not just possible but easy:

= DateSerial(Year(Date()) - 1, 7, 1) AND DateSerial(Year(Date()), 7, 1)

--

John W. Vinson [MVP]

On Tue, 1 Dec 2009 08:08:01 -0800, BABs
wrote:

I need to build a query (to be used for eternity) to return records for dates
between 01 July (the year prior) and 30 Jun (the current year) based on the
date when the query is run.
Is this possible?

Not just possible but easy:

= DateSerial(Year(Date()) - 1, 7, 1) AND DateSerial(Year(Date()), 7, 1)

--

John W. Vinson [MVP]

I assumed you want to GENERATE all the date; if you already have the records
and wish to limit yourself to the interval, use one of the other
propositions.

I missed a Day( ) in my where clause:

WHERE iota= iif(Day(DateSerial( 1+Year( DateAdd("m", -6, now()) ), 2, 29 ))
=29 , 366, 365)



Vanderghast, Access MVP


"vanderghast" vanderghast@com wrote in message
...
If you have a driver table, a table with one field, with values for 0 to
366 (at least). Say that table is called Iotas, and its field, iota, also
its primary key.

--- untested---
SELECT DateSerial( Year( DateAdd("m", -6, now( ) )), 7, 1) + Iotas.Iota
FROM Iotas
WHERE iota= iif(DateSerial( 1+Year( DateAdd("m", -6, now()) ), 2, 29 )
=29 , 366, 365)




The idea is to check if this is a leap year or not (to take 366 or 365
days), and to add all of them to the first of July of the proper year.



Vanderghast, Access MVP



"BABs" wrote in message
...
I need to build a query (to be used for eternity) to return records for
dates
between 01 July (the year prior) and 30 Jun (the current year) based on
the
date when the query is run.
Is this possible?

I assumed you want to GENERATE all the date; if you already have the records
and wish to limit yourself to the interval, use one of the other
propositions.

I missed a Day( ) in my where clause:

WHERE iota= iif(Day(DateSerial( 1+Year( DateAdd("m", -6, now()) ), 2, 29 ))
=29 , 366, 365)



Vanderghast, Access MVP


"vanderghast" vanderghast@com wrote in message
...
If you have a driver table, a table with one field, with values for 0 to
366 (at least). Say that table is called Iotas, and its field, iota, also
its primary key.

--- untested---
SELECT DateSerial( Year( DateAdd("m", -6, now( ) )), 7, 1) + Iotas.Iota
FROM Iotas
WHERE iota= iif(DateSerial( 1+Year( DateAdd("m", -6, now()) ), 2, 29 )
=29 , 366, 365)




The idea is to check if this is a leap year or not (to take 366 or 365
days), and to add all of them to the first of July of the proper year.



Vanderghast, Access MVP



"BABs" wrote in message
...
I need to build a query (to be used for eternity) to return records for
dates
between 01 July (the year prior) and 30 Jun (the current year) based on
the
date when the query is run.
Is this possible?

I assumed you want to GENERATE all the date; if you already have the records
and wish to limit yourself to the interval, use one of the other
propositions.

I missed a Day( ) in my where clause:

WHERE iota= iif(Day(DateSerial( 1+Year( DateAdd("m", -6, now()) ), 2, 29 ))
=29 , 366, 365)



Vanderghast, Access MVP


"vanderghast" vanderghast@com wrote in message
...
If you have a driver table, a table with one field, with values for 0 to
366 (at least). Say that table is called Iotas, and its field, iota, also
its primary key.

--- untested---
SELECT DateSerial( Year( DateAdd("m", -6, now( ) )), 7, 1) + Iotas.Iota
FROM Iotas
WHERE iota= iif(DateSerial( 1+Year( DateAdd("m", -6, now()) ), 2, 29 )
=29 , 366, 365)




The idea is to check if this is a leap year or not (to take 366 or 365
days), and to add all of them to the first of July of the proper year.



Vanderghast, Access MVP



"BABs" wrote in message
...
I need to build a query (to be used for eternity) to return records for
dates
between 01 July (the year prior) and 30 Jun (the current year) based on
the
date when the query is run.
Is this possible?

Awesome....thx

"John W. Vinson" wrote:

On Tue, 1 Dec 2009 08:08:01 -0800, BABs
wrote:

I need to build a query (to be used for eternity) to return records for dates
between 01 July (the year prior) and 30 Jun (the current year) based on the
date when the query is run.
Is this possible?

Not just possible but easy:

= DateSerial(Year(Date()) - 1, 7, 1) AND DateSerial(Year(Date()), 7, 1)

--

John W. Vinson [MVP]
.

Awesome....thx

"John W. Vinson" wrote:

On Tue, 1 Dec 2009 08:08:01 -0800, BABs
wrote:

I need to build a query (to be used for eternity) to return records for dates
between 01 July (the year prior) and 30 Jun (the current year) based on the
date when the query is run.
Is this possible?

Not just possible but easy:

= DateSerial(Year(Date()) - 1, 7, 1) AND DateSerial(Year(Date()), 7, 1)

--

John W. Vinson [MVP]
.

Awesome....thx

"John W. Vinson" wrote:

On Tue, 1 Dec 2009 08:08:01 -0800, BABs
wrote:

I need to build a query (to be used for eternity) to return records for dates
between 01 July (the year prior) and 30 Jun (the current year) based on the
date when the query is run.
Is this possible?

Not just possible but easy:

= DateSerial(Year(Date()) - 1, 7, 1) AND DateSerial(Year(Date()), 7, 1)

--

John W. Vinson [MVP]
.