title: MS-Access Between Dates Query
body: <p>I have a query that is working fine with the exception of not calculating between certain dates.  My initial Query SQL is </p>
  
  <pre><code>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)&gt;DateSerial(Year(Date()),5,20)))
  GROUP BY Roster.UserID, SchedulingLog.Category
  HAVING (((SchedulingLog.Category) Like "DH*" Or (SchedulingLog.Category) Like "Comp*"));
  </code></pre>
  
  <p>This SQL works fine until the new year.  I have modified my code in the <code>WHERE</code> statement to use a <code>BETWEEN</code> qualifier, but I can not get it to work.  Here is the new SQL...</p>
  
  <pre><code>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*"));
  </code></pre>
  
  <p>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?</p>