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
631 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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 

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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
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 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…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

708 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now