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

Gustav, I'm in awe!!! But I don't fully understand how it works. What does
the:

Nz((Select Top 1 Val(Right(TextID,3))

return, and how does checking it being >1 spot the gaps?

--
Andy Lacey
http://www.minstersystems.co.uk



--------- Original Message --------
From: Access Developers discussion and problem solving
<accessd at databaseadvisors.com>
To: Access Developers discussion and problem solving
<accessd at databaseadvisors.com>
Subject: Re: [AccessD] Checking for "holes" in a sequence using SQL
Date: 04/11/04 12:23

>
>     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
>
> --
> _______________________________________________
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
>
>
>
>
>

________________________________________________
Message sent using UebiMiau 2.7.2