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
643 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
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
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 

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

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

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

When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
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…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

820 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