title: Extract specific query record and assign to variable in VBA
body: <p>I have a query inside a VBA sub which using some previously defined date variables, produces a recordset with 5 rows of data that follow the below structure: </p>
  
  <p><strong>ID | Currency | Date | Rate</strong></p>
  
  <p>1 | EUR | 24/05/2018 | 1.24</p>
  
  <p>2 | EUR | 23/05/2018 | 1.23</p>
  
  <p>3 | EUR | 22/05/2018 | 1.22</p>
  
  <p>4 | EUR | 21/05/2018 | 1.21</p>
  
  <p>5 | EUR | 20/05/2018 | 1.20</p>
  
  <pre><code>Query = "SELECT Hist.ID, Hist.Currency, Hist.DateR, Hist.Rate FROM Hist WHERE Hist.Currency= " &amp; CCY &amp; " AND Hist.DateR= #" &amp; TD &amp; "#" OR " Hist.Currency= " &amp; CCY &amp; " AND Hist.DateRef= #" &amp; Date_1 &amp; "#" OR " Hist.Currency= " &amp; CCY &amp; " AND Hist.DateR= #" &amp; Date_2 &amp; "#" OR " Hist.Currency= " &amp; CCY &amp; " AND Hist.DateR= #" &amp; Date_3 &amp; "#" OR " Hist.Currency= " &amp; CCY &amp; " AND Hist.DateR= #" &amp; Date_4 &amp; "#"
  </code></pre>
  
  <p>I need to assign the Rate for each Date to a separate variable; XRate, Date1Rate, Date2Rate, Date3Rate, Date4Rate. Previously to assign a variable to a record in the query I done the following:</p>
  
  <pre><code>Set rs = CurrentDb.OpenRecordset(Query)
      XRate = rs("Rate")
  </code></pre>
  
  <p>However this would only work if I split the query into 5 separate queries, one for each date and assign as above. I'm wondering whether there is a smarter way to do this, with the result being 5 Variables each containing the rate for a specific date.</p>
  
  <p>Any help or guidance is appreciated, thank you in advance. </p>