x
• Status: Solved
• Priority: Medium
• Security: Public
• Views: 447

# Sequence gap in alpha-numeric field

I have a field with data formated : AB002200, AB002201, AB002203, AB002204, AB002206, etc. The first two characters are always AB followed by 6 numbers. I want a formula to find gaps in the numeric sequence portion of this data. (for example AB002205 is missing in my sample data) I have Crystal Reports 8.5
0
toddcolson
• 7
• 7
1 Solution

Commented:
Try this
Put the field in the detail section
Right Click the left margin of the detail section
Click FORMAT SECTION
Click the format button next to SUPPRESS
Formula is
PreviousIsNull({YourField}) or (ToNumber(Right({YourField,6)) - 1 = (Previous(ToNumber(Right({YourField,6)))

mlmcc

0

Author Commented:
I get matching } for this field name is missing, if I add the brackets to the field name I get the ) is missing.
0

Commented:
Sorry { } go around the Crystal field names.

Try this one
PreviousIsNull({YourField}) or (ToNumber(Right({YourField},6)) - 1 = (Previous(ToNumber(Right({YourField},6)))))

mlmcc
0

Author Commented:
I get field is required here message after:    Previous(
0

Commented:
That's what I get for copying.

PreviousIsNull({YourField}) or (ToNumber(Right({YourField},6)) - 1 = (ToNumber(Right(Previous({YourField},6)))))

mlmcc
0

Author Commented:
Now: To many arguments have been given to this function.
0

Commented:
Sorry.

PreviousIsNull({YourField}) or (ToNumber(Right({YourField},6)) - 1 = (ToNumber(Right(Previous({YourField}),6))))

mlmcc
0

Author Commented:
Now: The string is non numeric
0

Author Commented:
Wait, It works!, Now I got it. Thanks
0

Author Commented:
It works, but I need a formula that creates a report that continues to show all of the data in the field and somehow marking the gaps. Is that possible?
0

Commented:
Yes.  How do you want the gaps marked?

mlmcc
0

Author Commented:
Maybe like a formula field with the word missing at the gap. Or highlighting. Any report that keeps all the data in the field while at the same time showing the gaps.
0

Commented:
Let me think about it.  Rather late here now.

mlmcc
0

Commented:
Do you still need assistance with this?

mlmcc
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.