date calculation with full date in separate columns

Question

I'm trying to make a query for the last 3 months of an item with my month and year in separate columns like so:

YEAR_       PERIOD
2014           5
2013           6
2013           11
2011           6
2009           2

The query needs to always start from the current month and year. I've tried using DateAdd(), DateSerial(), and DateDiff() none of those worked. Whenever I try to use month(now()-3) i'm getting 2 instead of 11.

Answer 1

Adding or subtracting integers and dates simply adds or subtracts days from the date. So Now() - 3 results in 2016-02-15 (it is 2016-02-18 at the time of this posting). That is clearly still the month of February - hence your result of two.

Give this a try Month(DateAdd("m", -3, Now)). Here we are adding -3 months to the current date and then getting the resulting month. Based on today's date that will result in 11.

Answer 2

I figured it out.

DateDiff("m",CDate(Format([PERIOD] & "/" & [YEAR_],"mm/yyyy")),Now()) 

This took the two fields and made them a single date. I then took the difference from this month and the months between the two dates. I then set the criteria to <= 3.

Addendum

It can be simplified to:

DateDiff("m",CDate([PERIOD] & "/" & [YEAR_]),Date()) 

In general, however, you should never use string handling for dates if it can be avoided, and it easily can:

DateDiff("m",DateSerial([YEAR_],[PERIOD],1)),Date())