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

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