MS-Access 2003基于表达式限制查询结果

Question

我有一个拉取假期摘要信息的查询。我想将结果限制为晚于当前周年纪念日期。当我仅使用周年纪念表达式运行查询时，周年纪念表达式工作正常。当我放入Where子句时，它会提示周年纪念。在没有Where子句的情况下，is执行表达式并显示周年日期。

SELECT SchedulingLog.UserID, SchedulingLog.Category, Sum(IIf([CatDetail] Like 'Ann*',[Value],0)) AS Gain, Sum(IIf([CatDetail] Like '*Used*',[Value],0))+Sum(IIf([CatDetail] Like 'Adj*',[Value],0)) AS Used, [Gain]+[Used] AS [Left], Month([WM DOH]) & "/" & Day([WM DOH]) & "/" & Year(Date()) AS Anniversary
FROM SchedulingLog INNER JOIN Roster ON SchedulingLog.UserID = Roster.UserID
WHERE (((SchedulingLog.EventDate)>=[Anniversary]))
GROUP BY SchedulingLog.UserID, SchedulingLog.Category, Month([WM DOH]) & "/" & Day([WM DOH]) & "/" & Year(Date())
HAVING (((SchedulingLog.Category) Like "Vac*"));

我正在寻找的是一种方法，将求和的值限制为表达式周年纪念日之后的事件日期。

基于我对SQL缺乏经验和指导的极大耐心。我开始看到曙光了。所以当我拿着

DateAdd("yyyy",1,[WM DOH])
it returns the individuals date of Hire and the year advances by 1.
ex. employee DOH 12-25-2003
The expression returns 12-25-2004

我正在将dateAdd调整为如下所示

DateAdd("yyyy",Year(Date())-1,[WM DOH])
This returns something completely wrong. but illustrates what I am trying to accomplish
desired result ex. 12-25-2011

有什么想法？

Answer 1

同样的故事。年度不是可以在where子句中引用的字段，它是一个别名。

WHERE SchedulingLog.EventDate>= 
    DateSerial(Year(Date())-1,Month([WM DOH]) ,Day([WM DOH]))