Parameter query all years

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.
 
T

Tom van Stiphout

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
 
J

John Spencer

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.
 
O

order800

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.
 
K

KARL DEWEY

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top