?
Solved

Crystal Reports - Formula error when no records exist

Posted on 2011-09-28
7
Medium Priority
?
432 Views
Last Modified: 2012-05-12
I have a crystal report that runs nightly as a fixed length text file. I have one field that errors when no records exist on the report. The formula is bascially stacked on four other forumulas. Any ideas how I can get the formula to not error when no records are found?

The error is on formula @LoanAmountFinal2 in the else statement
error:  String length is less than 0 or not an integer

1)  left({@LoanAmountFinal2}+space(11),11)

2)  formula   LoanAmountFinal2

shared numbervar thefield;
thefield:= len({@LoanAmountFinal});

//display all the characters to the left of the field where the last three char are excluded
if isnull({@LoanAmountFinal})or {@LoanAmountFinal} = "NA"or {@LoanAmountFinal} = "0" or {@LoanAmountFinal} = "" then ""
else left({@LoanAmountFinal},thefield-3)

3)  formula LoanAmountFinal

shared numbervar thefield;
thefield:= len({@LoanAmountString});

//display all the characters to the left of the field where the last three char are exclude
if isnull({@LoanAmountString}) or {@LoanAmountString}="" then ""
else right({@LoanAmountString},thefield-1)

4)  formula  LoanAmountString

if (IsNull({@Note_Amount_HMDA}))then "NA"
else if {@Note_Amount_HMDA} < 1 then "NA"
else ToText({@Note_Amount_HMDA})

5)  formula Note_Amount_HMDA

round({HMDA.Note $} * 0.001)



0
Comment
Question by:asmyatt
7 Comments
 
LVL 18

Expert Comment

by:vasto
ID: 36722867
check if @LoanAmountFinal2 returns null
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 36727468
Change this formula
5)  formula Note_Amount_HMDA

If IsNull({HMDA.Note $}) then
    0
Else
    round({HMDA.Note $} * 0.001)

What ar ethee formulas doing?
Perhaps there is a way to accomplish it without using all the formulas.

mlmcc


0
 
LVL 2

Author Comment

by:asmyatt
ID: 36815177
vasto -  it errors out because no records exist

mlmcc - I tried changing 5) formula, but still got  "String lengh is less than 0 or not an integer
 
The formulas first place the amount field in the thousand position, then turn the amount into a string and "NA" of no amount or '0', then return anything left of the decimal.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 101

Expert Comment

by:mlmcc
ID: 36815358
Are all 5 formulas on the report for display?

WHat do you have as the default for numbers?
Is it 0 or 1 decimals?
Your code assumes it is 2

How about using Truncate rather than round?

mlmcc

0
 
LVL 2

Author Comment

by:asmyatt
ID: 36816522
Formula 1 is the only one displayed on the report (Fixed length text file).

It would be 2 decimals unless NULL or not records exist when the report is executed. That is when the error occurs when no records exist when the report is executed.

We need to round the number.
0
 
LVL 101

Accepted Solution

by:
mlmcc earned 1000 total points
ID: 36816947
You can use

Truncate({NumberField} + 0.5) to round


I am trying to figure out what the formulas are doing and why the need for that many especially since you don't use them in the report except to calculate intermediate values.

Are you trying to get the value in 1000s?

Is this all in one report or is there a subreport?

mlmcc
0
 
LVL 35

Assisted Solution

by:James0628
James0628 earned 1000 total points
ID: 36890761
I agree with mlmcc.  If you're not displaying the intermediate formulas, then why not combine everything in one formula?  It would make it a _lot_ easier to follow.  FWIW, I've also had problems trying to use one formula inside another.  I had a complicated process that I tried to break down into pieces (formulas), and for some reason it just didn't seem to work correctly, and I ended up putting it all in one formula.  I've no particular reason to think that that has anything to do with your problem.  It's just another reason to consider combining your formulas.

 Putting all of that aside, #3 does not look right to me.  If #4 produces "NA", then it looks to me like #3 will see that it's not null or "", so it will take the rightmost (length - 1 = 2 - 1 = 1) characters, so #3 will produce "A".  I believe that that could produce the error in #2, because it would not recognize "A" and would try to take the left 1 - 3 = -2 characters.

 Now, how to fix it.

 In #3, you could change

if isnull({@LoanAmountString}) or {@LoanAmountString}="" then ""
else right({@LoanAmountString},thefield-1)

   to

if {@LoanAmountString} = "NA" then "NA"
else right({@LoanAmountString},thefield-1)


 You do an IsNull test in @LoanAmountString.  You shouldn't need another one in #3.  And I don't see any way that @LoanAmountString can produce "", so I don't think you need that test either.

 FWIW, you should really also change/delete the comment in #3.  It was obviously just copied from #2, and doesn't apply to #3.

 I would also change the "if" in #2 from

if isnull({@LoanAmountFinal})or {@LoanAmountFinal} = "NA"or {@LoanAmountFinal} = "0" or {@LoanAmountFinal} = "" then ""
else left({@LoanAmountFinal},thefield-3)

   to something like

if {@LoanAmountFinal} = "NA" or {@LoanAmountFinal} = "0" then ""
else left({@LoanAmountFinal},thefield-3)


 As with #3, you did an IsNull test earlier, so I don't think you need another one in #2.  And if you make the change that I suggested to #3, I don't think it will ever produce "", in which case you don't need that test in #2 either.

 James
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Hello again, all.  For those of you that have been following along, you'll know that this is my third article on this topic (though it is not Part III).  This article is sort of remedial, and probably the topic with which I should have started the s…
This script will sweep a range of IP addresses (class c only, 255.255.255.0) and report to a log the version of office installed. What it does: 1.)      Creates log file in the directory the script is run from (if it doesn't already exist) 2.)      Sweep…
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…
Loops Section Overview
Suggested Courses
Course of the Month15 days, 17 hours left to enroll

850 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