In Crystal Reports XI, How do I Select records from a field by the second character in that field?

In a report I am writing, I need to pull only the records that have a zero or a "C" as the second character in a specific field.  Any suggestions?  I was hoping a  SELECT CASE (MID {FIELD1}, 2, 1) = 0)  or SELECT CASE (MID {FIELD1}, 2, 1) = "C") would work, but I'm not doing something right.  
PorterConsultingLLCAsked:
Who is Participating?
 
Kurt ReinhardtConnect With a Mentor Sr. Business Intelligence Consultant/ArchitectCommented:
The simplest possible way would be to include the following in your record selection criteria:

{table.field} [2] in ['0','C']

That being said, this won't ever be passed to the database for processing.  In order for it to be passed in the WHERE clause Crystal Reports generates, it would need to specifically be a database field.  Without changing your entire report design, you could pretty easily do this with a SQL Expression field.  There may be a MID or SUBSTR function available to you in the function list of the SQL Expression editor.  Create a SQL Expression using the appropriate function - the only purpose of the expression is to return the second character of the string.  Then, you could modify your selection criteria to look like this:

{%field} in  ['0','C']

~Kurt
0
 
JayConverseCommented:
Shouldn't it be:

MID {FIELD1}, 2, 1) = "0"

The zero is a character
0
 
JayConverseCommented:
Oops, missing left paren:

MID ({FIELD1}, 2, 1) = "0"
0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
PorterConsultingLLCAuthor Commented:
I'm not sure how to do this.  In the Report/Select Expert, is there a way to tuck this in?  I am NOT working with a SQL database, but a legacy flatfile db for which I link the tables in CR.  This means I cannot do a view in SQL that would allow the select to happen before it gets to CR.
0
 
mlmccConnect With a Mentor Commented:
You just do it in the select expert using the formula option.

Crystal will handle the filtering

mlmcc
0
 
JayConverseConnect With a Mentor Commented:
Close the select expert, and select Report/Selection Formulas/Record.  Enter this:

MID ({FIELD1}, 2, 1) in ["0","A"]

If there is already text in the formula, preface that with "and"
0
 
PorterConsultingLLCAuthor Commented:
Thank you all, VERY MUCH!  It worked and I can move onto the myriad of other reports waiting for me!

BLESS YOU!
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.

All Courses

From novice to tech pro — start learning today.