[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