access query that returns records for previous year up to same day, same month as today but for previous year

Question

I need help specifying an access criteria on a date field that would pull records from my database from the beginning of last year, 1/1/2014 to a date that has the same day, and same month as today. The reason for this information is to be able to able to compare year-to-date records(and later counts) for this year to year-to-date's count for last year... thus, if today's date is 8/20/2015, I would want to be able to pull from 1/1/2015 to 8/20/2015 and then compare it to 1/1/2014 to 8/20/2014. Just for heads-up, I am using the same query and form to count records based on weekly, quarterly date-ranges, and so I cannot use textboxes with "Start" and "End" dates. Also, I cannot pre-specify any date in my query. Any idea will be greatly appreciated. Thank you all.

Answer 1

To get last year's year-to-date DateSerial will do what you want.

Where [DateColumn] >= DateSerial(year(now)-1,1,1) 
      and [DateColumn] <= DateSerial(year(now)-1,month(now),day(now))

Another option

Where [DateColumn] >= dateadd("yyyy", datediff("yyyy", 0, now)-2, 2 )
      and [DateColumn <= DateAdd("yyyy",-1, now)

Answer 2

You need to use Date() in SQL:

Where [DateColumn] >= DateSerial(Year(Date())-1,1,1) 
      And [DateColumn] <= DateAdd("yyyy",-1,Date())

Answer 3

The following expression can be used as the criteria for the date field in the query designer

>="01/01/" & (Year(Date())-1) AND <=Day(Date()) & "/" & Month(Date()) & "/" & Year(Date())-1

Warning: using strings to build dates should be avoided when possible. DateSerial() is a better approach, but this will work in MS Access (Jet/ACE).