Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 444
  • 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
[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

 
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

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

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