If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
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] |
#12
|
|||
|
|||
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] |
#13
|
|||
|
|||
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] |
#14
|
|||
|
|||
Return a date range based on today's date
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? |
#15
|
|||
|
|||
Return a date range based on today's date
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? |
#16
|
|||
|
|||
Return a date range based on today's date
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? |
#17
|
|||
|
|||
Return a date range based on today's date
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] . |
#18
|
|||
|
|||
Return a date range based on today's date
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] . |
#19
|
|||
|
|||
Return a date range based on today's date
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] . |
|
Thread Tools | |
Display Modes | |
|
|