title: Get the missing value in a sequence of numbers
body: <p>I made the following query for the SQL Server backend</p>
  
  <pre><code>SELECT TOP(1) (v.rownum + 99)
  FROM
      (
          SELECT incrementNo-99 as id, ROW_NUMBER() OVER (ORDER BY incrementNo) as rownum
          FROM proposals
          WHERE [year] = '12'
      )  as v
  WHERE v.rownum &lt;&gt; v.id
  ORDER BY v.rownum
  </code></pre>
  
  <p>to find the first unused proposal number.
  (It's not about the lastrecord +1)</p>
  
  <p>But I realized <code>ROW_NUMBER</code> is not supported in access.
  I looked and I can't find something similar.</p>
  
  <p>Does anyone know how to get the same result as a <code>ROW_NUMBER</code> in access?</p>
  
  <p>Maybe there's a better way of doing this.</p>
  
  <p>Actually people insert their proposal No (incrementID) with no constraint. This number looks like this 13-152. xx- is for the current year and the -xxx is the proposal number. The last 3 digits are supposed to be incremental but in some case maybe 10 times a year they have to skip some numbers. That's why I can't have the auto increment.</p>
  
  <p>So I do this query so when they open the form, the default number <strong>is the first unused</strong>.</p>
  
  <p>How it works:</p>
  
  <p>Because the number starts at 100, I do -99 so it starts at 1.</p>
  
  <p>Then I compare the row number with the id so it looks like this</p>
  
  <pre><code>ROW NUMBER      |       ID
  1                        1                 (100)
  2                        2                 (101)
  3                        3                 (102)
  4                        5                 (104)&lt;--------- WRONG  
  5                        6                 (105)
  </code></pre>
  
  <p>So now I know that we skip 4. So I return (4 - 99) = 103</p>
  
  <p>If there's a better way, I don't mind changing but I really like this query.</p>
  
  <p>If there's really no other way and I can't simulate a row number in access, i will use the pass through query.</p>
  
  <p>Thank you </p>