>Hi.
>Is there any syntax of a Select command that will higghlight mssing numbers.
>I have a table which has a field which should be sequential and I want to highlight if there are any msiing numbers.
>I can do this by writing a program but wondered if there was a way to do this using select synatax ?
>Regards
>Gerard

I think writing a program is the best approach. The problem with SQL is that for a NOT IN, for example, you would need to construct a larger set, which again requires coding.

One thing you can do with SQL is the following: for each sequence number, get the next sequence number, and compare. This would only give you the first missing number if there are several consecutive numbers, but it may be a starting point for the user to check. Something like this:

select Sequence;
  from MyTable;
  into Tmp1;
  nofilter
select Sequence + 1 as Sequence;
  from MyTable;
  into Tmp2;
  nofilter
select Sequence from Tmp2;
  where Sequence not in Tmp1

And then alert the user that if there are several missing numbers, you are only showing the first in the sequence. Or use some additional (non-SQL?) coding from there, to get additional sequence numbers. You might even use SQL to get, for each missing number, the next higher number that does exist - and show the range of missing numbers. After all, you may not need to actually generate 10 consecutive missing numbers; instead, you might tell the users to check the situation.