CODEWITHSUNDEEP
sqlms-access-2003
Desert Spider
Desert Spider

Reputation: 778

MS-Access 2003 Limiting a query result based on an expression

I have a query that pulls vacation summary information. I would like to limit the results to greater than current anniversary date. When I riun the query with only the anniversary expression the anniversary expression works fine. When I put in the Where clause it prompts for Anniversary. With out the Where clause is performs the exp[ression and presents the Anniversary date. 

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*"));

What I am looking for is a way to limit the summed values to after the Expression Anniversary, for the Event Date.

Based on Remous patience with my SQL inexperience and guidance. I am begining to see the light. So when I take 

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

I am adjusting the dateAdd to something like this

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

Thoughts?

Upvotes: 0

Views: 351

Answers (1)

Fionnuala
Fionnuala

Reputation: 91306

Same story. Anniversary is not a field that you can refer to in the where clause, it is an alias.

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

Upvotes: 2

Related Questions