Return a date range based on today's date

[BABs]

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?

 

[Jeff Boyce]

Take a look at the DateSerial() function in Access HELP. It should provide
you the mechanism for doing what you're describing.

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[vanderghast]

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

 

[John Spencer]

Yes it is possible. The criteria would be

Between DateSerial(Year(Date())-1,7,1) and DateSerial(Year(Date()),6,30)

So for today (December 1, 2009) that will generate records for
July 1, 2008 to June 30, 2009

On Jan 1, 2010 that will generate records for
July 1,2009 to June 30, 2010

If you want Jan 1, 2010 to return
July 1, 2008 to June 30, 2009
you can adjust that and use
Between DateSerial(Year(DateAdd("m",-6,Date()))-1,7,1)
AND DateSerial(Year(DateAdd("m",-6,Date())),6,30)
Which should shift year ranges on July 1.



John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[John W. Vinson]

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:

 

[vanderghast]

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

 

[BABs]

Awesome....thx

Not just possible but easy: