Subtract 1 Year from Query Date Range

[pushrodengine via AccessMonster.com]

I’m using:

Between [Forms].[frmReport].[Start] And [Forms].[frmReport].[End]

in criteria to input a date range into a query. The date is entered into the
form “frmReport†in the format of 00/00/0000.

Is there a way to modify the expression above so that it will subtract one
year from the “Start†date and one year from the “End†date.

I need to query one year earlier than the year entered into the form.

Thanks

 

[Douglas J. Steele]

Between DateAdd("yyyy", -1, [Forms].[frmReport].[Start]) And
DateAdd("yyyy", -1, [Forms].[frmReport].[End])

 

[Guest]

Try using datediff

Between DateDiff("yyyy",-1,SomeDateHere) and DateDiff("yyyy",-1,
AnotherDateHere)

Good luck

 

[pushrodengine via AccessMonster.com]

Tried both suggestions and both did not work.

 

[pushrodengine via AccessMonster.com]

Is there something else I could try?

 

[Douglas J. Steele]

What does "did not work" mean in this context? Did you get an error? If so,
what was the error? If you didn't get an error, what's the symptom you're
observing, and what are you expecting instead?

 

[pushrodengine via AccessMonster.com]

What does "did not work" mean in this context?

Between DateAdd("yyyy", -1, [Forms].[frmReport].[Start]) And DateAdd("yyyy", -
1, [Forms].[frmReport].[End])

When I use: DateDiff or DateAdd functions within the query criteria no
results appear.

Did you get an error?

No, I don't get an Error, but when I ran the query it is empty. What I should
see is a query of records one year eariler than what was entered into the
form "frmReport" [Start] and [End], but I actually don't get any records in
the query.

What are you expecting instead?

The user would enter 1/1/2007 in [Start] and 1/12/2007 [End] within the form
"frmReport".
Meanwhile, within the query "qry09" the date range would change to one year
earlier or 1/1/2006 and 1/12/2006.

Thank you for your help.

 

[Douglas J. Steele]

What is 1/12/2007 to you? To Access (regardless of what your Short Date
format may be set to through Regional Settings), that's 12 January, 2007.

In queries, you must use mm/dd/yyyy format (or an unambiguous format such as
dd mmm yyyy or yyyy-mm-dd).

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

What does "did not work" mean in this context?

Between DateAdd("yyyy", -1, [Forms].[frmReport].[Start]) And
DateAdd("yyyy", -
1, [Forms].[frmReport].[End])

When I use: DateDiff or DateAdd functions within the query criteria no
results appear.

Did you get an error?

No, I don't get an Error, but when I ran the query it is empty. What I
should
see is a query of records one year eariler than what was entered into the
form "frmReport" [Start] and [End], but I actually don't get any records
in
the query.

What are you expecting instead?

The user would enter 1/1/2007 in [Start] and 1/12/2007 [End] within the
form
"frmReport".
Meanwhile, within the query "qry09" the date range would change to one
year
earlier or 1/1/2006 and 1/12/2006.

Thank you for your help.

 

[pushrodengine via AccessMonster.com]

The query works without the subtraction of the year! That is to say, not the
way I would like it to.

In queries, you must use mm/dd/yyyy format (or an unambiguous format such as
dd mmm yyyy or yyyy-mm-dd).

Regradless, of whether I enter 1/12/2007 or 01/12/2007 (mm/dd/yyyy) the query
works beautifully, the way it should normally. But what I really need is to
subtract one year from each entry.

What is 1/12/2007 to you? To Access (regardless of what your Short Date
format may be set to through Regional Settings), that's 12 January, 2007.

So, how would I subtract one year form 12 January, 2007?

Thanks

 

[pushrodengine via AccessMonster.com]

I finally figured it out! Works perfect!

To: Douglas J. Steele and Wayne-I-M thank you both for your time and help.