Solved

# Crystal Reports - Formula error when no records exist

Posted on 2011-09-28
413 Views
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
Question by:asmyatt
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points

LVL 18

Expert Comment

ID: 36722867
check if @LoanAmountFinal2 returns null
0

LVL 101

Expert Comment

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

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

LVL 101

Expert Comment

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

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

mlmcc earned 250 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

James0628 earned 250 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

Question has a verified solution.

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

I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
When it comes to writing scripts for a Client/Server computing environment it is essential to consider some way of enabling the authentication functionality within a script. This sort of consideration mainly comes into the picture when we are dealin…
This video Micro Tutorial shows how to password-protect PDF files with free software. Many software products can do this, such as Adobe Acrobat (but not Adobe Reader), Nuance PaperPort, and Nuance Power PDF, but they are not free products. This vide…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
###### Suggested Courses
Course of the Month3 days, 21 hours left to enroll