CODEWITHSUNDEEP
sqlms-accessms-access-2003
Desert Spider
Desert Spider

Reputation: 778

MS-Access Between Dates Query

I have a query that is working fine with the exception of not calculating between certain dates. My initial Query SQL is 

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

This SQL works fine until the new year. I have modified my code in the WHERE statement to use a BETWEEN qualifier, but I can not get it to work. Here is the new SQL...

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

The intent of the new SQL is to gather the information between last year 5-20-2012 and this year 5-20-2013. Where am I missing the boat?

Upvotes: 0

Views: 2360

Answers (1)

Christian Specht
Christian Specht

Reputation: 36431

Your mistake is here:

DateSerial(Year(Date()-1),5,20)

It should be:

DateSerial(Year(Date())-1,5,20)

Just output both statements in the Immediate Window to see the difference.

Your code (Year(Date()-1)) subtracts 1 from today's date (3th January 2013 - 1 = 2th January 2013!!) and takes the year from that (2013), so the result is 20th May 2013.
So you are effectively loading the data from 20th May 2013 to 20th May 2013!

My code (Year(Date())-1) gets the year of today's date (2013) and subtracts 1 from it (--> 2012), so the result is 20th May 2012.

Upvotes: 2

Related Questions