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

Wow! Thanks Gustav! Great SQL!

JR

-----Original Message-----
From: Gustav Brock [mailto:gustav at cactus.dk]
Sent: Thursday, November 04, 2004 4:52 AM
To: Access Developers discussion and problem solving
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

--
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com



This electronic transmission is strictly confidential to TNCO, Inc. and
intended solely for the addressee. It may contain information which is
covered by legal, professional, or other privileges. If you are not the
intended addressee, or someone authorized by the intended addressee to
receive transmissions on behalf of the addressee, you must not retain,
disclose in any form, copy, or take any action in reliance on this
transmission. If you have received this transmission in error, please notify
the sender as soon as possible and destroy this message. While TNCO, Inc.
uses virus protection, the recipient should check this email and any
attachments for the presence of viruses. TNCO, Inc. accepts no liability for
any damage caused by any virus transmitted by this email.