CODEWITHSUNDEEP
sqlms-access
Desert Spider
Desert Spider

Reputation: 778

WHERE sql integrating an IIF

I am having trouble getting a Where statement to work when I try an IIF statement. I have tried a few variations of the sql to no avail. What I am trying to do is count holidays Gain up to a certain date. When I use my Between statement alone it works fine. When I try to reset the counter after the system clock goes past 5-20-13, I return no results when it should show 1 for the Memorial Day holiday that just passed.The segment of code that I can not figure out the proper syntax on is 

WHERE (((SchedulingLog.EventDate)=IIf(Date()>DateSerial(Year(Date()),5,20),((SchedulingLog.EventDate) Between DateSerial(Year(Date()),5,20) And DateSerial(Year(Date())+1,5,20)),((SchedulingLog.EventDate) Between DateSerial(Year(Date())-1,5,20) And DateSerial(Year(Date()),5,20)))))

Below is my entire sql.

SELECT Roster.UserID, SchedulingLog.Category, 
Sum(IIf([CatDetail] Like '*Gain*',[Value],0)) AS Gain, DateSerial(Year(Date()),5,20) AS ResetDate, Date() AS [Date]
FROM SchedulingLog INNER JOIN Roster ON SchedulingLog.UserID = Roster.UserID
WHERE (((SchedulingLog.EventDate)=IIf(Date()>DateSerial(Year(Date()),5,20),((SchedulingLog.EventDate) Between DateSerial(Year(Date()),5,20) And DateSerial(Year(Date())+1,5,20)),((SchedulingLog.EventDate) Between DateSerial(Year(Date())-1,5,20) And DateSerial(Year(Date()),5,20)))))
GROUP BY Roster.UserID, SchedulingLog.Category, DateSerial(Year(Date()),5,20), Date()
HAVING (((SchedulingLog.Category) Like "DH*" Or (SchedulingLog.Category) Like "Comp*"));

Any assistance would be greatly appreciated.

Upvotes: 0

Views: 113

Answers (1)

Hart CO
Hart CO

Reputation: 34784

I believe the problem is twofold, first you're looking for where your DATE = BETWEEN Date and Date. Not sure if that's a problem in Access, syntax is usually WHERE Date BETWEEN Date and Date. 2nd issue is trying to return a command in the IIF statement, rather than returning values to be evaluated by the BETWEEN statement.

I believe this should work:

SELECT Roster.UserID, SchedulingLog.Category, 
Sum(IIf([CatDetail] Like '*Gain*',[Value],0)) AS Gain, DateSerial(Year(Date()),5,20) AS ResetDate, Date() AS [Date]
FROM SchedulingLog INNER JOIN Roster ON SchedulingLog.UserID = Roster.UserID
WHERE SchedulingLog.EventDate BETWEEN IIf(Date()>DateSerial(Year(Date()),5,20)
                                                ,DateSerial(Year(Date()),5,20)
                                                ,DateSerial(Year(Date())-1,5,20))
                             AND IIf(Date()>DateSerial(Year(Date()),5,20)
                                                ,DateSerial(Year(Date())+1,5,20)
                                                ,DateSerial(Year(Date()),5,20))
GROUP BY Roster.UserID, SchedulingLog.Category, DateSerial(Year(Date()),5,20), Date()
HAVING (((SchedulingLog.Category) Like "DH*" Or (SchedulingLog.Category) Like "Comp*"));

Upvotes: 2

Related Questions