Using the NOW Function In Access

Question

I am setting up a code to pull all employees hired within the last 2 years who got a certain rating. I have been looking into the YEAR(NOW()) function but I am having a difficult time setting it up. I need to use the NOW function because I need it to pull the data from the time the user access the query. The ratings are completed every following feburary (i.e 2013 ratings will be completed in February of 2014) so it needs to read something like

YEAR(NOW()-12) but it

This way if I were to run it today it would go back and pull the ratings for 2012 and 2011 since 2013 have not yet been completed.

My whole code looks like:

SELECT dbo_v_TMS_QPR_01_Score.TMS_ID, dbo_v_TMS_QPR_01_Score.QPR_Year, dbo_v_TMS_QPR_01_Score.Final_QPR_Score
FROM O867IA_VJOBHST INNER JOIN dbo_v_TMS_QPR_01_Score ON O867IA_VJOBHST.SYS_EMP_ID_NR = dbo_v_TMS_QPR_01_Score.GEMSID
WHERE (((dbo_v_TMS_QPR_01_Score.Final_QPR_Score)>="1.25") AND ((O867IA_VJOBHST.EMP_ACN_TYP_CD)="HIR") AND ((O867IA_VJOBHST.REC_EFF_STT_DT)=Year(Now()-12)))
GROUP BY dbo_v_TMS_QPR_01_Score.TMS_ID, dbo_v_TMS_QPR_01_Score.QPR_Year, dbo_v_TMS_QPR_01_Score.Final_QPR_Score;

But I keep getting the error: INCONSISTENT DATATYPES: EXPECTED DATE GOT NUMBER (#932)

Answer 1

What you have does not work. It subtracts 12 days off the current date/time and then converts it to the year. Thus, it returns 2013.

Use the dataadd() function. The following is a blank query in the query designer.

I am asking for today's date minus 12 months. See output below.