title: Order of records that DLookup uses to return first value (Access VBA)
body: <p>long time stalker but first time poster here so apologies for any social faux pas I make.</p>
  
  <p>I am trying to use DLookup to search for a record in a table using VBA. The specific record I am after is the one closest in date to sdate (a user specified date) which also meets some other criteria. There are likely to be a couple of records with dates prior to sdate which meet the same criteria, and I am only interested in the one which is chronologically closest to sdate.</p>
  
  <p>The following is a simplified example of the code I am using to try and achieve this. I use baseTestString as there are quite a few similar DLookup expressions so it saves typing and clarifies the code slightly (to me at least).</p>
  
  <pre><code>    DoCmd.OpenTable ("Results")
      DoCmd.SetOrderBy "[Survey_Date] Desc"
      DoCmd.Close acTable, ("Results")
  
      'set a new criteria for baseline testing using Dlookup
      basetestString = "[Equipment_ID] = '" &amp; equipID &amp; "' AND [Baseline?] = True _
          AND     format([Survey_Date],""ddmmyyyy"") &lt; format(" _
          &amp; sdate &amp; ",""ddmmyyyy"")"
  
      'set variables
      [Forms]![results]![text_A] = Nz(DLookup("[Input]", "[results]", _
          basetestString))
  </code></pre>
  
  <p>I believed (perhaps naively) that DLookup returns the first record it finds that matches the criteria specified. So, my code was designed to sort the table into chronological order, thinking that this would be the order that DLookup would cycle through the records. </p>
  
  <p>However, each time I run the code, I am returned the lowest possibly date that matches the criteria rather than the one closest to sdate. After some playing around, I believe that DLookup uses the primary key as its basis for cycling through the records (the earlier dates are entered earlier, and hence given a primary key using autonumber which is lower than later dates).</p>
  
  <p>This leads to me my questions...</p>
  
  <p>1) I am correct in believing this is what is happening when I am returned the wrong record?</p>
  
  <p>2) Is there a way to use DLookup in the way I am attempting? Can I choose which field is used to order the records for DLookup? Assigning the date field as the primary key is not possible as the dates might not always be unique.</p>
  
  <p>3) Is there any other way I can achieve what I am trying to do here?</p>
  
  <p>Thank you very much</p>