Improve company productivity with a Business Account.Sign Up

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

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
Asked:
toddcolson
  • 7
  • 7
1 Solution
 
mlmccCommented:
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
 
toddcolsonAuthor 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
 
mlmccCommented:
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
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
toddcolsonAuthor Commented:
I get field is required here message after:    Previous(
0
 
mlmccCommented:
That's what I get for copying.

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

mlmcc
0
 
toddcolsonAuthor Commented:
Now: To many arguments have been given to this function.
0
 
mlmccCommented:
Sorry.

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

mlmcc
0
 
toddcolsonAuthor Commented:
Now: The string is non numeric
0
 
toddcolsonAuthor Commented:
Wait, It works!, Now I got it. Thanks
0
 
toddcolsonAuthor 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
 
mlmccCommented:
Yes.  How do you want the gaps marked?

mlmcc
0
 
toddcolsonAuthor 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
 
mlmccCommented:
Let me think about it.  Rather late here now.

mlmcc
0
 
mlmccCommented:
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.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 7
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now