Finding missing sequential numbers

[butkus]

I need a formula that returns missing sequential numbers.
The numbers however are in string format

D00000001
D00000002
etc..

I found Ken's formula for data in number format but I can't seems to adapt it for my use.

What I've tried

tonumber(mid({case_number},2,8))-1

extracts the number fine, but the second formula

next{case_number}
tonumber(mid({case_number},2,8))+1

doesn't return what I thought it would.

I've also tried assigning these values to numbervar's and can't seem to do anything with those either.

As always, any help is greatly appreciated.

James

 

[synapsevampire]

Perhaps:

"next{case_number}
tonumber(mid({case_number},2,8))+1"

should be:

tonumber(mid(next({case_number}),2,8))+1

I haven't looked at Ken's example (though I've done this in the past and wish now that I had a sample), but the idea I used was that if the next()-1 <> current value, I'd use an if to construct the output, as in:

if next(val(mid({myfield},2,100)))-1 = val(mid({myfield},2,100))+1 then
totext(val(mid({myfield},2,100))+1)

else

if next(val(mid({myfield},2,100)))-1 > val(mid({myfield},2,100))+1 then
totext(val(mid({myfield},2,100))+1) + totext(next(val(mid({myfield},2,100)))-1)

Something like that, I didn't test.

Hope this helps. I'd post the example but I don't have it here.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[butkus]

It's asking for a {data field} to be placed after the first next(

If the next function is moved to the following, it says too many arguments exist:

If val(mid(next({myfield},2,100)))-1

Any ideas?

James

 

[synapsevampire]

Try:

If val(mid(next({myfield}),2,100))-1

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[butkus]

I got it to work

But I had to write 5 formula's to do it.

#1 returns the string value minus the leading D
#2 returns the string value of the NEXT record minus the leading D
#3 converts #1 to a number +1
#4 converts #2 to a number -1
#5 converts #2 to a number (as a test)
then the details section is supressed when #3 = #5

The next function does not play nice when converting strings to numbers - is all I can tell.

Thanks for the help

James