|
| 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 |
|
#1
December 1st, 2009, 04:08 PM
posted to microsoft.public.access.queries
|
|||
|
|||
Return a date range based on today's date
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? 
|
|
#2
December 1st, 2009, 04:59 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
Return a date range based on today's date
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. "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?
|
|
#3
December 1st, 2009, 04:59 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
Return a date range based on today's date
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. "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?
|
|
#4
December 1st, 2009, 04:59 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
Return a date range based on today's date
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. "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?
|
|
#5
December 1st, 2009, 05:08 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
Return a date range based on today's date
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?
|
|
#6
December 1st, 2009, 05:08 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
Return a date range based on today's date
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?
|
|
#7
December 1st, 2009, 05:08 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
Return a date range based on today's date
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?
|
|
#8
December 1st, 2009, 05:13 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
Return a date range based on today's date
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 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?
|
|
#9
December 1st, 2009, 05:13 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
Return a date range based on today's date
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 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?
|
|
#10
December 1st, 2009, 05:13 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
Return a date range based on today's date
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 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?
|
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
