[AccessD] Checking for "holes" in a sequence using SQL

    Excellent - Gustav!   Beautiful SQL.

A.D.Tejpal
--------------

  ----- Original Message ----- 
  From: Gustav Brock 
  To: Access Developers discussion and problem solving 
  Sent: Thursday, November 04, 2004 15:22
  Subject: Re: [AccessD] Checking for "holes" in a sequence using SQL


  Hi Joe

  > If I have data similar to:
  > JOE001
  > JOE002
  > JOE003
  > JOE005
  > JOE006

  > Is there a way, using just SQL, to return the next available number in the
  > sequence, JOE004, and be flexible enough to return JOE007 if JOE004 had
  > already existed?
  > Or do I have to use code to iterate through the records to check for
  > "holes"?

  Except for the indication of some malfunctioning during the design
  state as already pointed out by others, there is:

  <SQL>

    SELECT TOP 1
      TextID,
      Left(TextID, Len(TextID)-3) &
        Right(String(3,"0") & Right(TextID,3)+1,3) AS NextID
    FROM
      tblID
    WHERE
      (Nz(
        (Select Top 1
          Val(Right(TextID,3))
        From
          tblID As T
        Where
          T.TextID > tblID.TextID)
        -Val(Right([TextID],3)),
      2)>1)
    ORDER BY
      tblID.TextID;

  </SQL>

  All the "3"s count for the length of the numeric right part of the
  textid. 
  Note the "magic 2" in Nz() which cares for the return of the last
  textid if no "holes" exist.

  /gustav