Solved

Writing a formula in Crystal Reports

Posted on 2014-03-16
7
717 Views
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.
Quick-Code.txt
0
Comment
Question by:gilnari
7 Comments
 
LVL 22

Expert Comment

by:Ido Millet
Comment Utility
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]
        eLSE
        sTest[1] & " " & sTest[2] & " " & sTest[3];
    )
    Else "Not Numric";
);
0
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
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

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

mlmcc
0
 

Author Comment

by:gilnari
Comment Utility
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.
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 22

Expert Comment

by:Ido Millet
Comment Utility
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.
0
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
Try something like

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

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

mlmcc
0
 
LVL 34

Accepted Solution

by:
James0628 earned 500 total points
Comment Utility
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:

WhilePrintingRecords;
Global numberVar nTemp;
Global stringVar sText;
sText:= LEFT({RESULT.ATTRIBUTE_1}, 3);
if NumericText (sText) then
  nTemp := ToNumber(sText);
IF NOT ISNULL ({RESULT.ATTRIBUTE_3}) THEN 
    IF NumericText (sText) and nTemp < 100 THEN
    " " & ({RESULT.ATTRIBUTE_1} & " " & {RESULT.ATTRIBUTE_2} & " " & {RESULT.ATTRIBUTE_3})
    ELSE
     ({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.

 James
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

I use more than 1 computer in my office for various reasons. Multiple keyboards and mice take up more than just extra space, they make working a little more complicated. Using one mouse and keyboard for all of my computers makes life easier. This co…
In our personal lives, we have well-designed consumer apps to delight us and make even the most complex transactions simple. Many enterprise applications, however, are a bit behind the times. For an enterprise app to be successful in today's tech wo…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
The viewer will learn how to successfully download and install the SARDU utility on Windows 7, without downloading adware.

771 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

10 Experts available now in Live!

Get 1:1 Help Now