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

I get it, but I wouldn't have dreamed that up in a million years. Hats off
to you Gustav.

--
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 14:49

>
> Hi Andy, Mark and A.D
>
> > 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?
>
> Thanks!
>
> It uses the numeric part of Joe's textid to find the next higher id
> and the difference between these. This difference is, of course, 1
> where no holes exist. If a hole is present, the difference will be 2
> or larger.
> However, if no holes are present, no difference is found and it is
> Null and the expression with (Select ..)-Val(..) will be Null as well.
> This is caught by Nz() here:
>
>   Nz((Select ..)-Val(Right([TextID],3)),2)
>
> which returns 2 for the "missing" hole at the last textid.
> Finally textid is sorted and Top 1 is added to find the first hole
> and only this.
>
> /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