Query Critera for 2 Years Ago

[unclefink]

I am trying to create a query to return data from 2 years ago. I found critera to return data for a year ago which is working. I'd figured after some slight manipulation it work work for a two year time frame and well so far no success. I tried Year([caseDate])=Year(Date())-2

The criteria I am currently using for "last year" is
Year([caseDate])=Year(Date())-1

Ultimately what I am trying to do is create a report that will show a history of cases and comparison over the past three years. I've got current year, last year, but now i need 2 years ago.

In this case, 2011, 2012, 2013.

Any suggestions would help immensly.

 

[Alansidman]

Try using the DateAdd Function

Look here: http://www.techonthenet.com/access/functions/date/dateadd.php

 

[unclefink]

That doesnt seem to work either. I would like the query to return all data from two years prior, in this case 2011 without having to manually change the dates every month or year.

The "last year" criteria works perfect, but now I need the data for two years ago.

 

[Big Pat]

Hi,

Your criteria looks right to me and in fact works for me. But I wonder if you're using it on the right expression. I created an expression to get the year of the casedate first and then applied the criteria to that.

See various queries in attached database. Does this achieve what you need?

Pat.

 

[unclefink]

Pat,

Thank you very much for the direction. It appears as though you are right as to your suspicion of me putting the criteria in the wrong spot. Rather than breaking it up into the "calculated field" and then setting the criteria, I had it all in the field calculation part.

After looking at your sample, I realized what you were talking about and made the adjustments and whalaaaa. Thank you very much for your direction.

Alan, Thank you very much for attempting to help me diagnose this issue, outside reccomendation/help is always welcome.

 

[Big Pat]

You're welcome. It's nice for me to be the one answering a question for a change. I'm always asking!!

 

[Brianwarnock]

how does this thread differ from this one

http://www.access-programmers.co.uk/forums/showthread.php?t=239575

Looks like double post to me, must remember not to waste my time on this guy in future.

Brian

 

[unclefink]

Mr. Warnock,

The intention was not to double post as you presume to be the case. The initial thread was referencing a criteria string I was using in attempt to get data over a period of three years. Current year and last year was working fine, when attempting to get the second year back, it wasnt working. With the greatly and much appreciated help the issue was solved when Big Pat identified and pointed out my issue. That thread was solved. Also that specific thread was referencing data in general for a three year time period.

The secondary thread was referencing "Year to Date" which I thankfully received help from thus gather all data from day one of the year to a secondary date to be determined by several options which i have yet to decide.

If you would like to read the posts again to realize your Duh, feel free. I did not; nor did anyone on this thread or forum make you respond to anything to "waste your time as you so put it". If thats the way you feel, DONT POST!

 

[Brianwarnock]

As you are searching on dates you have presumably got an index on that field, the use of the function Year prevents the use of that index thus reducing the efficiency of the program, the efficient approach is to use Between .. And ..
For the 1/1 and 31/12 of each year, using Dateserial , ie the approach I suggested in the other thread.

Brian