find gaps in number range

[DBAFrog]

I need to find the gaps in a number range.

i.e. for a given month we use the number range 1000 to 2000, but we don't use 1012 because it was canceled. I need to summurize the column CLOG# to find the gap between 1011 and 1013 so that we can note the missing number.

I've been trying sum and count with various groupings to find the answer, but no luck.

Someone has to have done this before. Any information or help appreciated.

thanks,

B.

 

[mbarron]

Here are the formulas I use to show gaps in a series of numbers. You have to get a little fancier if you want to show gaps in the beginning or end of the list.
===========================================================
@showgap

if {@gap 2}=0 then "" else
if previous({@next}) = totext({tblData.ControlNo}-1,0) then
previous({@next})
else
previous({@next}) &" to " & totext({tblData.ControlNo}-1,0)
===========================================================
@gap 2

{tblData.ControlNo}-1 - previous({tblData.ControlNo})
===========================================================
@next
totext({tblData.ControlNo}+1,0)
===========================================================

You then would need to suppress the lines where there are no gaps
Use the conditional formatting (x-2) button for Suppress.
==========================================================
if onfirstrecord then true else if {@gap 2}=0 then true
==========================================================


Mike

 

[DBAFrog]

Mike;

Thank you very much...This is EXACTLY what I needed.

I Never would have figured This out.


Brad