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

I also was impressed...and from looking at it...you could use the same 
concept to fill in missing dates in a given date range.

Thanks for the direction.

Mark A. Matte

>From: "Andy Lacey" <andy at minstersystems.co.uk>
>Reply-To: 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: Thu, 4 Nov 2004 15:21:01 +0100
>
>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
>
>--
>_______________________________________________
>AccessD mailing list
>AccessD at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/accessd
>Website: http://www.databaseadvisors.com