Writing a formula in Crystal Reports

Posted on 2014-03-16
Last Modified: 2014-04-02
I am a bit out of practice in the use of Crystal and could use a jump start.  Been a long time since I wrote crystal formulas and could use help in correcting the code I have attached.

I need to pull the first three values in a string, determine if the first three values is all numeric and if so is it less then a 100 using a Crystal Formula.   If not a string or less than a 100 use the first concant of values, if greater then 100 then use the second.
Question by:gilnari
LVL 22

Expert Comment

by:Ido Millet
ID: 39934049
stringVar sTest := "023abc";
IF Len(sTest) < 3 Then "Too Short" Else
    sTest := LEFT(sTest, 3);

    IF NumericText(sTest) Then
        IF val(sTest) < 100 Then " " & sTest[1] & " " & sTest[2] & " " & sTest[3]
        sTest[1] & " " & sTest[2] & " " & sTest[3];
    Else "Not Numric";
LVL 100

Expert Comment

ID: 39934188
What does this part of your question mean?

>>If not a string or less than a 100 use the first concant of values, if greater then 100 then use the second

The final Else in the formula above should be

        sTest[1] & " " & sTest[2] & " " & sTest[3];


Author Comment

ID: 39934828
I have a string as example 110 deg F or could be 70 deg F.  
What I need to know if the first three characters is something like  (110, or 70<space>)
Then see if the value (which is numeric) is less than 100 (100 deg F) I want to add a space to a set of values I am concanting together at the beginning if less than 100.   Its a pretty lineup thing I am trying to accomplish.
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

LVL 22

Expert Comment

by:Ido Millet
ID: 39934839
The text sample you provided indicates you want spaces inserted, but your latest message indicates otherwise.  Tough to follow, so please provide a couple of examples of input versus desired output.
LVL 100

Expert Comment

ID: 39935010
Try something like

Local StringVar  TestString;
TestString := Left({YourFIeld},3);

If NumberText(YourString) then
    If Val(YourString) < 100 then
         " " & TestString

LVL 34

Accepted Solution

James0628 earned 500 total points
ID: 39935180
Some of the other suggestions may give you what you need, but FWIW ...

 I see 3 basic problems with the formula attached to your first post.

 You don't have a ";" at the end of each statement.
 You had a typo in the declaration for the string variable and called it sTest, instead of sText (the name you used in the other lines).
 You used "=" when you tried to set nTemp, instead of ":=".

 You also mentioned checking to see if the first 3 characters are numeric, and you didn't do that in your formula.  If those characters could really be non-numeric (not just something like "72 ", but things like "abc" or "1b3"), then you need to test for that, because ToNumber will give you an error on strings like that.

 This is your formula with the corrections I mentioned, and a couple of NumericText checks added:

Global numberVar nTemp;
Global stringVar sText;
if NumericText (sText) then
  nTemp := ToNumber(sText);
    IF NumericText (sText) and nTemp < 100 THEN
    " " & ({RESULT.ATTRIBUTE_1} & " " & {RESULT.ATTRIBUTE_2} & " " & {RESULT.ATTRIBUTE_3})

Open in new window

 If you want the formula to produce a specific result (not just an empty string) when RESULT.ATTRIBUTE_3 is null, then you also need to add an Else at the end for that.


Featured Post

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

Suggested Solutions

Today, still in the boom of Apple, PC's and products, nearly 50% of the computer users use Windows as graphical operating systems. If you are among those users who love windows, but are grappling to keep the system's hard drive optimized, then you s…
In this article, you will read about the trends across the human resources departments for the upcoming year. Some of them include improving employee experience, adopting new technologies, using HR software to its full extent, and integrating artifi…
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…

770 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