title: convert sql server query to be used in Access database
body: <p>I have found this query to work with the SQL server very well.
  So I tought to port this to an Access database.Fields are the same.</p>
  
  <pre><code>Rate_Start_Date = DateTime
  Rate_End_Date = DateTime
  Room_Type = char
  Rate = Currency
  Price_List_Code = Integer
  </code></pre>
  
  <p>However, the sql syntax is beyond my knowledge.
  Can someone help me rewrite this so it works with an Access database ?</p>
  
  <p>The code :</p>
  
  <pre><code>    use MYDATABASE
  DECLARE @StartDate  DATETIME,
          @EndDate    DATETIME,
          @RoomType   VARCHAR(6),
          @PriceListCode    INT
  ;
  
   SELECT @StartDate  = :a2,
          @EndDate    = :a3,
          @RoomType   = :a1,
          @PriceListCode  = :a4
  ;
  WITH 
  cteStayDates AS
  ( 
   SELECT RoomType  = Room_Type,
          StartDate = CASE WHEN Rate_Start_Date &lt; @StartDate THEN @StartDate ELSE Rate_Start_Date END,
          EndDate   = CASE WHEN Rate_End_Date   &gt; @EndDate   THEN @EndDate   ELSE Rate_End_Date   END,
          Rate
     FROM dbo.Room_Rates
    WHERE @RoomType  = Room_Type
      AND @StartDate &lt; Rate_End_Date
      AND @EndDate  &gt;= Rate_Start_Date
      AND @PriceListCode = PRICE_LIST_CODE
  ) 
   SELECT RoomType, StartDate, EndDate, Rate, 
          Days = DATEDIFF(dd,StartDate,EndDate)
               + CASE WHEN EndDate = @EndDate THEN 0 ELSE 0 END
     FROM cteStayDates
    ORDER BY StartDate
  ;
  </code></pre>