Parameter query all years

  • Thread starter Thread starter order800
  • Start date Start date
O

order800

I want to run a parameter query [Access 2003] on date that includes all
years. Ex: Date = (10/20/2008). I want to view all records between (10/10/all
years) and (10/30/all years). The parameter itself is easy but I’m lost as to
how to include all years.
 
On Thu, 23 Oct 2008 19:19:00 -0700, order800

.... where Month(MyDateField)=10 and Day(MyDateField) between 10 and 30

-Tom.
Microsoft Access MVP
 
And if you want to do plus or minus 10 days from the date, please post back
and explain that as the solution will necessarily be different if you are
going across months.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
On Thu, 23 Oct 2008 19:19:00 -0700, order800

... where Month(MyDateField)=10 and Day(MyDateField) between 10 and 30

-Tom.
Microsoft Access MVP

I want to run a parameter query [Access 2003] on date that includes all
years. Ex: Date = (10/20/2008). I want to view all records between (10/10/all
years) and (10/30/all years). The parameter itself is easy but I’m lost as to
how to include all years.
Click to expand...
Click to expand...
 
That works, but now I see that the question was not specific enough. I want
to view historical seasonal records that occur around same time every year
and view all years.

If Now() is the 5th day, and parameter Between Now() -15 days And Now() +15
days, I need the 20th day of previous month to the 20th day of current month.
The year end is another problem: if Now() is the 5th of January, then I need
to include December of previous year in -15 day parameter.

Simply put, I want to run a query on dates with a parameter of 15 days on
each side of Now() to show historical annual records for that time of year.

Thanks



Tom van Stiphout said:
On Thu, 23 Oct 2008 19:19:00 -0700, order800

.... where Month(MyDateField)=10 and Day(MyDateField) between 10 and 30

-Tom.
Microsoft Access MVP

I want to run a parameter query [Access 2003] on date that includes all
years. Ex: Date = (10/20/2008). I want to view all records between (10/10/all
years) and (10/30/all years). The parameter itself is easy but I’m lost as to
how to include all years.
Click to expand...
Click to expand...
 
Create a table named CountNumber with field CountNUM containing numbers 0
(zero) through your maximum spread of years. Add the table without joining
in your query.
Use this as criteria --
Between
DateSerial(Year(DateAdd("yyyy",-[CountNUM],Date())),Month(Date()),Day(Date()))-10
And
DateSerial(Year(DateAdd("yyyy",-[CountNUM],Date())),Month(Date()),Day(Date()))+10
--
KARL DEWEY
Build a little - Test a little


order800 said:
That works, but now I see that the question was not specific enough. I want
to view historical seasonal records that occur around same time every year
and view all years.

If Now() is the 5th day, and parameter Between Now() -15 days And Now() +15
days, I need the 20th day of previous month to the 20th day of current month.
The year end is another problem: if Now() is the 5th of January, then I need
to include December of previous year in -15 day parameter.

Simply put, I want to run a query on dates with a parameter of 15 days on
each side of Now() to show historical annual records for that time of year.

Thanks



Tom van Stiphout said:
On Thu, 23 Oct 2008 19:19:00 -0700, order800

.... where Month(MyDateField)=10 and Day(MyDateField) between 10 and 30

-Tom.
Microsoft Access MVP

I want to run a parameter query [Access 2003] on date that includes all
years. Ex: Date = (10/20/2008). I want to view all records between (10/10/all
years) and (10/30/all years). The parameter itself is easy but I’m lost as to
how to include all years.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top