在Access中使用NOW功能 [英] Using the NOW Function In Access

问题描述

我正在设置一个代码，以拉近过去两年中获得一定评级的所有雇员.我一直在研究YEAR(NOW())函数，但是我很难设置它.我需要使用NOW函数，因为我需要它从用户访问查询时开始提取数据.评分会在接下来的2月完成(即2013年的评分将于2014年2月完成)，因此需要阅读

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

这样，如果我今天运行它，它将回溯并拉高2012和2011年的评分，因为2013年尚未完成.

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.

我的整个代码如下:

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;

但是我一直收到错误消息:不一致的数据类型:预期日期编号(#932)

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

推荐答案

您所拥有的不起作用.它会从当前日期/时间中减去12天，然后将其转换为年份.因此，它将返回2013.

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.

使用dataadd()函数.以下是查询设计器中的空白查询.

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

我要的是今天的日期减去12个月.参见下面的输出.

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

这篇关于在Access中使用NOW功能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！