parameter query for greater than date

[tjb]

I'd like a query to return results greater than six weeks from whatever date
I enter (or preferably the date I am running the query). Right now I'm
manually editing the query every time I run it to say ">#04/14/2008# but I
know Access has to have a way to automate this. Thanks!

 

[KARL DEWEY]

For 6 weeks from today use ---

DateAdd("ww",6,Date())

For 6 weeks from date you enter use ---

DateAdd("ww",6,CVDate([Enter start date for search]))

 

[tjb]

That didn't seem to work, could you explain what each piece of that code is
doing? What does the "ww" do?

For 6 weeks from today use ---

DateAdd("ww",6,Date())

For 6 weeks from date you enter use ---

DateAdd("ww",6,CVDate([Enter start date for search]))

--
KARL DEWEY
Build a little - Test a little

I'd like a query to return results greater than six weeks from whatever date
I enter (or preferably the date I am running the query). Right now I'm
manually editing the query every time I run it to say ">#04/14/2008# but I
know Access has to have a way to automate this. Thanks!

 

[KARL DEWEY]

What does the "ww" do?
It is used in date functions like 'd', 'm', or 'q' for day of month, month
of year, and quarter of year. "w" will give you day of week whereas 'ww' is
week of the year. In this case it is adding 6 weeks.

Did you place one of there in the criteria row of the query design view
grid? That is where you would use them.
--
KARL DEWEY
Build a little - Test a little

That didn't seem to work, could you explain what each piece of that code is
doing? What does the "ww" do?

For 6 weeks from today use ---

DateAdd("ww",6,Date())

For 6 weeks from date you enter use ---

DateAdd("ww",6,CVDate([Enter start date for search]))

--
KARL DEWEY
Build a little - Test a little

I'd like a query to return results greater than six weeks from whatever date
I enter (or preferably the date I am running the query). Right now I'm
manually editing the query every time I run it to say ">#04/14/2008# but I
know Access has to have a way to automate this. Thanks!

 

[tjb]

I actually have two date fields I was going to use this with in the same
query and when I enter ">DateAdd("ww",6,Date())" into both fields, it returns
nothing.

When I enter the criteria manually as ">#4/17/2008#" it returns what I want.
Any ideas?

It is used in date functions like 'd', 'm', or 'q' for day of month, month
of year, and quarter of year. "w" will give you day of week whereas 'ww' is
week of the year. In this case it is adding 6 weeks.

Did you place one of there in the criteria row of the query design view
grid? That is where you would use them.
--
KARL DEWEY
Build a little - Test a little

That didn't seem to work, could you explain what each piece of that code is
doing? What does the "ww" do?

For 6 weeks from today use ---
DateAdd("ww",6,Date())

For 6 weeks from date you enter use ---
DateAdd("ww",6,CVDate([Enter start date for search]))

--
KARL DEWEY
Build a little - Test a little


:

I'd like a query to return results greater than six weeks from whatever date
I enter (or preferably the date I am running the query). Right now I'm
manually editing the query every time I run it to say ">#04/14/2008# but I
know Access has to have a way to automate this. Thanks!

 

[KARL DEWEY]

when I enter ">DateAdd("ww",6,Date())" into both fields, it returns nothing.
Your orignal post said "I'd like a query to return results greater than six
weeks from whatever date I enter (or preferably the date I am running the
query)."
Your entry of >#04/14/2008# is not six weeks from 'date of entry' but 6
weeks ago, big difference.

If you use the second one I posted then it will be from the date you enter.

By the way if you use >#04/14/2008# then it will not pull a record dated
#04/14/2008 as it is not greater. You might want to use >=#04/14/2008# for
greater than OR equal.
--
KARL DEWEY
Build a little - Test a little

I actually have two date fields I was going to use this with in the same
query and when I enter ">DateAdd("ww",6,Date())" into both fields, it returns
nothing.

When I enter the criteria manually as ">#4/17/2008#" it returns what I want.
Any ideas?

What does the "ww" do?

It is used in date functions like 'd', 'm', or 'q' for day of month, month
of year, and quarter of year. "w" will give you day of week whereas 'ww' is
week of the year. In this case it is adding 6 weeks.

Did you place one of there in the criteria row of the query design view
grid? That is where you would use them.
--
KARL DEWEY
Build a little - Test a little

That didn't seem to work, could you explain what each piece of that code is
doing? What does the "ww" do?

:

For 6 weeks from today use ---
DateAdd("ww",6,Date())

For 6 weeks from date you enter use ---
DateAdd("ww",6,CVDate([Enter start date for search]))

--
KARL DEWEY
Build a little - Test a little


:

I'd like a query to return results greater than six weeks from whatever date
I enter (or preferably the date I am running the query). Right now I'm
manually editing the query every time I run it to say ">#04/14/2008# but I
know Access has to have a way to automate this. Thanks!

 

[tjb]

Ideally I'd like it to return any record with a date after 6 weeks prior to
the date I am running the query. So if I run the query today, it would
return anything after 4/17/08. I'm not too worried about the greater than or
equal part.

Your orignal post said "I'd like a query to return results greater than six
weeks from whatever date I enter (or preferably the date I am running the
query)."
Your entry of >#04/14/2008# is not six weeks from 'date of entry' but 6
weeks ago, big difference.

If you use the second one I posted then it will be from the date you enter.

By the way if you use >#04/14/2008# then it will not pull a record dated
#04/14/2008 as it is not greater. You might want to use >=#04/14/2008# for
greater than OR equal.
--
KARL DEWEY
Build a little - Test a little

I actually have two date fields I was going to use this with in the same
query and when I enter ">DateAdd("ww",6,Date())" into both fields, it returns
nothing.

When I enter the criteria manually as ">#4/17/2008#" it returns what I want.
Any ideas?

What does the "ww" do?
It is used in date functions like 'd', 'm', or 'q' for day of month, month
of year, and quarter of year. "w" will give you day of week whereas 'ww' is
week of the year. In this case it is adding 6 weeks.

Did you place one of there in the criteria row of the query design view
grid? That is where you would use them.
--
KARL DEWEY
Build a little - Test a little


:

That didn't seem to work, could you explain what each piece of that code is
doing? What does the "ww" do?

:

For 6 weeks from today use ---
DateAdd("ww",6,Date())

For 6 weeks from date you enter use ---
DateAdd("ww",6,CVDate([Enter start date for search]))

--
KARL DEWEY
Build a little - Test a little


:

I'd like a query to return results greater than six weeks from whatever date
I enter (or preferably the date I am running the query). Right now I'm
manually editing the query every time I run it to say ">#04/14/2008# but I
know Access has to have a way to automate this. Thanks!

 

[KARL DEWEY]

So use --

=DateAdd("ww",-6,Date())

This subtracts 6 weeks from today and then pulls all records after that.
--
KARL DEWEY
Build a little - Test a little

Ideally I'd like it to return any record with a date after 6 weeks prior to
the date I am running the query. So if I run the query today, it would
return anything after 4/17/08. I'm not too worried about the greater than or
equal part.

when I enter ">DateAdd("ww",6,Date())" into both fields, it returns nothing.

Your orignal post said "I'd like a query to return results greater than six
weeks from whatever date I enter (or preferably the date I am running the
query)."
Your entry of >#04/14/2008# is not six weeks from 'date of entry' but 6
weeks ago, big difference.

If you use the second one I posted then it will be from the date you enter.

By the way if you use >#04/14/2008# then it will not pull a record dated
#04/14/2008 as it is not greater. You might want to use >=#04/14/2008# for
greater than OR equal.
--
KARL DEWEY
Build a little - Test a little

I actually have two date fields I was going to use this with in the same
query and when I enter ">DateAdd("ww",6,Date())" into both fields, it returns
nothing.

When I enter the criteria manually as ">#4/17/2008#" it returns what I want.
Any ideas?

:

What does the "ww" do?
It is used in date functions like 'd', 'm', or 'q' for day of month, month
of year, and quarter of year. "w" will give you day of week whereas 'ww' is
week of the year. In this case it is adding 6 weeks.

Did you place one of there in the criteria row of the query design view
grid? That is where you would use them.
--
KARL DEWEY
Build a little - Test a little


:

That didn't seem to work, could you explain what each piece of that code is
doing? What does the "ww" do?

:

For 6 weeks from today use ---
DateAdd("ww",6,Date())

For 6 weeks from date you enter use ---
DateAdd("ww",6,CVDate([Enter start date for search]))

--
KARL DEWEY
Build a little - Test a little


:

I'd like a query to return results greater than six weeks from whatever date
I enter (or preferably the date I am running the query). Right now I'm
manually editing the query every time I run it to say ">#04/14/2008# but I
know Access has to have a way to automate this. Thanks!

 

[John Spencer]

Try using a negative number in the expression if you want to get
information that is in the past.

The expression DateAdd("ww",-6,Date())
will return a date that was 6 weeks PRIOR to today's date.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

Ideally I'd like it to return any record with a date after 6 weeks prior to
the date I am running the query. So if I run the query today, it would
return anything after 4/17/08. I'm not too worried about the greater than or
equal part.

when I enter ">DateAdd("ww",6,Date())" into both fields, it returns nothing.

Your orignal post said "I'd like a query to return results greater than six
weeks from whatever date I enter (or preferably the date I am running the
query)."
Your entry of >#04/14/2008# is not six weeks from 'date of entry' but 6
weeks ago, big difference.

If you use the second one I posted then it will be from the date you enter.

By the way if you use >#04/14/2008# then it will not pull a record dated
#04/14/2008 as it is not greater. You might want to use >=#04/14/2008# for
greater than OR equal.
--
KARL DEWEY
Build a little - Test a little

I actually have two date fields I was going to use this with in the same
query and when I enter ">DateAdd("ww",6,Date())" into both fields, it returns
nothing.

When I enter the criteria manually as ">#4/17/2008#" it returns what I want.
Any ideas?

:

What does the "ww" do?
It is used in date functions like 'd', 'm', or 'q' for day of month, month
of year, and quarter of year. "w" will give you day of week whereas 'ww' is
week of the year. In this case it is adding 6 weeks.

Did you place one of there in the criteria row of the query design view
grid? That is where you would use them.
--
KARL DEWEY
Build a little - Test a little


:

That didn't seem to work, could you explain what each piece of that code is
doing? What does the "ww" do?

:

For 6 weeks from today use ---
DateAdd("ww",6,Date())

For 6 weeks from date you enter use ---
DateAdd("ww",6,CVDate([Enter start date for search]))

--
KARL DEWEY
Build a little - Test a little


:

I'd like a query to return results greater than six weeks from whatever date
I enter (or preferably the date I am running the query). Right now I'm
manually editing the query every time I run it to say ">#04/14/2008# but I
know Access has to have a way to automate this. Thanks!