• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 456
  • Last Modified:

Crystal Reports - Formula error when no records exist

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
asmyatt
Asked:
asmyatt
2 Solutions
 
vastoCommented:
check if @LoanAmountFinal2 returns null
0
 
mlmccCommented:
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
 
asmyattAuthor Commented:
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
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
mlmccCommented:
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
 
asmyattAuthor Commented:
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
 
mlmccCommented:
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
 
James0628Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now