Year to Date Last Year - need query based on date field.

[jasonm]

I am a beginner so please keep it simple!!

I have a date field. I need a query that will give me the records that fall between the start of last year (January 1, 2014) and today's date but last year (June 7, 2014).

So today is June 7, 2015. I need to find the records with a date between January 1, 2014 to June 7, 2014.

Thanks for your help.

J

[Micron]

In the query design grid, try BETWEEN #01/01/2014# AND DATEADD("yyyy",-1,(DATE())
Not sure what the effect leap years would have on your results.

[jasonm]

Thanks for the quick response.

I didnt really want to hardcode in #01/01/2014#. How else could I do that?

BTW I dont really care about leap years - wont have a major effect on the numbers.

[jasonm]

Also in the DATEADD part it seems like we are missing a bracket.

[Micron]

Then what date if not 01/01/2014 as you requested? Will it always be Jan. 01 of the previous year? Will it be something that a user enters into a textbox control? Picks from a combo box list? You are correct - one more ) at the end.

[jasonm]

Sorry. Yes it will always be January 1 of the previous year.

[Micron]

BETWEEN DateSerial(DATEADD("yyyy",-1,DATE()),01,01) AND DATEADD("yyyy",-1,DATE())

I don't think the bracket before DATE() was required, so I did not add the bracket that was "missing". Hope I got it right.
TechOnTheNet is my go-to place for functions, especially for date/time ones.

[IrogSinta]

Correction on the DateSerial portion:

BETWEEN DateSerial(Year(DATE())-1, 1, 1) AND DATEADD("yyyy",-1,DATE())

Ron