title: Another Complicated Order By with Dates MS Access
body: <p>I need help in creating a query to sort the column Hour from 9am to 8am (the next day). </p>
  
  <p>I have union together 2 queries with separate order by and with date range parameters, and added rank to sort the returned rows. But it seems that the query return the rows of 9 to 23 query before returning the 0 to 8 query results. 
  <a href="https://i.stack.imgur.com/aYtOU.jpg" rel="nofollow noreferrer">unexpected result</a></p>
  
  <p>I have no idea on how to approach this as it is my first time to encounter this complex query. I have read a few threads about this and come up with this code.</p>
  
  <pre><code>SELECT StationCode, Month, Day, Year, Hour, Rainfall
  FROM
  (SELECT 1 AS rank, StationCode, Month, Day, Year, Hour, Rainfall, DateValue(CStr([Month] &amp; '/' &amp; [Day] &amp; '/' &amp; [Year])) AS [Date]
  FROM table1
  WHERE (((StationCode)=10) AND ((Hour)&gt;=9) AND ((DateValue(CStr([Month] &amp; '/' &amp; [Day] &amp; '/' &amp; [Year]))) Between DateValue([month1] &amp; '/' &amp; [day1] &amp; '/' &amp; [year1]) And DateValue([month2] &amp; '/' &amp; [day2] &amp; '/' &amp; [year2])))
  ORDER BY Month, Day, Year, Hour
  UNION
  SELECT 2 AS rank, StationCode, Month, Day, Year, Hour, Rainfall, DateValue(CStr([Month] &amp; '/' &amp; [Day] &amp; '/' &amp; [Year])) AS [Date]
  FROM table1
  WHERE (((StationCode)=10) AND ((Hour)&lt;=8) AND ((DateValue(CStr([Month] &amp; '/' &amp; [Day] &amp; '/' &amp; [Year]))) Between DateValue([month1] &amp; '/' &amp; [day1] &amp; '/' &amp; [year1]) And DateValue([month2] &amp; '/' &amp; [day2] &amp; '/' &amp; [year2])))
  ORDER BY Month, Day, Year, Hour) dt
  ORDER BY rank, Month, Day, Year
  </code></pre>
  
  <p>Sample Data :</p>
  
  <pre><code>StationCode | Month | Day | Year | Hour | Rainfall
           10 |     1 |   1 | 2010 |    0 |       0 
           10 |     1 |   1 | 2010 |    1 |       0
           10 |     1 |   1 | 2010 |    2 |       0
           10 |     1 |   1 | 2010 |    3 |       0
           10 |     1 |   1 | 2010 |    4 |       0
           10 |     1 |   1 | 2010 |    5 |       0
           10 |     1 |   1 | 2010 |    6 |       0
           10 |     1 |   1 | 2010 |    7 |       0
           10 |     1 |   1 | 2010 |    8 |       0
           10 |     1 |   1 | 2010 |    9 |       0
           10 |     1 |   1 | 2010 |   10 |       0
           10 |     1 |   1 | 2010 |   11 |       0
           10 |     1 |   1 | 2010 |   12 |       0
           10 |     1 |   1 | 2010 |   13 |       0
           10 |     1 |   1 | 2010 |   14 |       0
           10 |     1 |   1 | 2010 |   15 |       0
           10 |     1 |   1 | 2010 |   16 |       0
           10 |     1 |   1 | 2010 |   17 |       0
           10 |     1 |   1 | 2010 |   18 |       0
           10 |     1 |   1 | 2010 |   19 |       0
           10 |     1 |   1 | 2010 |   20 |       0
           10 |     1 |   1 | 2010 |   21 |       0
           10 |     1 |   1 | 2010 |   22 |       0
           10 |     1 |   1 | 2010 |   23 |       0
           10 |     1 |   2 | 2010 |    0 |       0
           10 |     1 |   2 | 2010 |    1 |       0
           10 |     1 |   2 | 2010 |    2 |       0
           10 |     1 |   2 | 2010 |    3 |       0
           10 |     1 |   2 | 2010 |    4 |       0
           10 |     1 |   2 | 2010 |    5 |       0
           10 |     1 |   2 | 2010 |    6 |       0
           10 |     1 |   2 | 2010 |    7 |       0
           10 |     1 |   2 | 2010 |    8 |       0
           10 |     1 |   2 | 2010 |    9 |       0
           10 |     1 |   2 | 2010 |   10 |       0
  so on...
  </code></pre>
  
  <p>Expected Results: </p>
  
  <pre><code>StationCode | Month | Day | Year | Hour | Rainfall
           10 |     1 |   1 | 2010 |    9 |       0
           10 |     1 |   1 | 2010 |   10 |       0
           10 |     1 |   1 | 2010 |   11 |       0
           10 |     1 |   1 | 2010 |   12 |       0
           10 |     1 |   1 | 2010 |   13 |       0
           10 |     1 |   1 | 2010 |   14 |       0
           10 |     1 |   1 | 2010 |   15 |       0
           10 |     1 |   1 | 2010 |   16 |       0
           10 |     1 |   1 | 2010 |   17 |       0
           10 |     1 |   1 | 2010 |   18 |       0
           10 |     1 |   1 | 2010 |   19 |       0
           10 |     1 |   1 | 2010 |   20 |       0
           10 |     1 |   1 | 2010 |   21 |       0
           10 |     1 |   1 | 2010 |   22 |       0
           10 |     1 |   1 | 2010 |   23 |       0
           10 |     1 |   2 | 2010 |    0 |       0
           10 |     1 |   2 | 2010 |    1 |       0
           10 |     1 |   2 | 2010 |    2 |       0
           10 |     1 |   2 | 2010 |    3 |       0
           10 |     1 |   2 | 2010 |    4 |       0
           10 |     1 |   2 | 2010 |    5 |       0
           10 |     1 |   2 | 2010 |    6 |       0
           10 |     1 |   2 | 2010 |    7 |       0
           10 |     1 |   2 | 2010 |    8 |       0
  </code></pre>
  
  <p>Thank you in advance.</p>