Subtract 1 Year from Query Date Range

  • Thread starter Thread starter pushrodengine via AccessMonster.com
  • Start date Start date
P

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
 
Between DateAdd("yyyy", -1, [Forms].[frmReport].[Start]) And
DateAdd("yyyy", -1, [Forms].[frmReport].[End])
 
Try using datediff

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

Good luck
 
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?
 
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.
 
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)


pushrodengine via AccessMonster.com said:
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.
 
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
 
I finally figured it out! Works perfect!

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

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

Back
Top