title: SQL RANK() equivalent for VBA ADODB.Recordset
body: <p>I'm trying to write an SQL query in VBA for an ADODB.Recordset object that generates a calculated rank field. Using transact-SQL does not work as seen in the sample here.</p>
  
  <pre><code>strSQL = "SELECT DISTINCT [MONTH-YYYY], Location, (SUM([O/T Earnings])/SUM([Monthly Earnings])) AS OT_AS_PCT "
  strSQL = strSQL &amp; "RANK() OVER (PARTITION BY [MONTH-YYYY] ORDER BY (SUM([O/T Earnings])/SUM([Monthly Earnings])) DESC) AS RANK "
  strSQL = strSQL &amp; "FROM [Overtime Data$] "
  strSQL = strSQL &amp; "GROUP BY [MONTH-YYYY], Location "
  
  rst_ranks.Open strSQL, cnn, adOpenStatic, adLockReadOnly
  </code></pre>
  
  <p>I've noticed when using functions in ADODB before that SQL functions do not work and VBA functions must be used in their place. Is there a VBA Rank() function that allows the PARTITION BY parameter.</p>
  
  <p>Thanks!</p>