Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2011-03-18
7
Medium Priority
?
671 Views
Last Modified: 2012-05-11
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.  
0
Comment
Question by:PorterConsultingLLC
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 12

Expert Comment

by:JayConverse
ID: 35167116
Shouldn't it be:

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

The zero is a character
0
 
LVL 12

Expert Comment

by:JayConverse
ID: 35167119
Oops, missing left paren:

MID ({FIELD1}, 2, 1) = "0"
0
 
LVL 26

Accepted Solution

by:
Kurt Reinhardt earned 668 total points
ID: 35167128
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

Author Comment

by:PorterConsultingLLC
ID: 35167653
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
 
LVL 101

Assisted Solution

by:mlmcc
mlmcc earned 668 total points
ID: 35167719
You just do it in the select expert using the formula option.

Crystal will handle the filtering

mlmcc
0
 
LVL 12

Assisted Solution

by:JayConverse
JayConverse earned 664 total points
ID: 35167766
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
 

Author Closing Comment

by:PorterConsultingLLC
ID: 35167997
Thank you all, VERY MUCH!  It worked and I can move onto the myriad of other reports waiting for me!

BLESS YOU!
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Suggested Courses

609 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question