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
646 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 167 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
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

 

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 100

Assisted Solution

by:mlmcc
mlmcc earned 167 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 166 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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Calculation - Mini/Max Removed then average 8 37
Macro problems with Excel file 6 52
Crystal Report with Dynamic Page Headers? 5 35
SQL Server 2012 to SQL Server 2016 24 54
Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…

734 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