Solved

Crystal Reports - Formula error when no records exist

Posted on 2011-09-28
7
401 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 100

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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 100

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 100

Accepted Solution

by:
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 34

Assisted Solution

by:James0628
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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Well hello again!  Glad to see you've made it this far without giving up.  In this, the fourth installment of my popular series, I'm going to cover functions and subroutines, what they are, and why they are useful.  Just in case you stumbled onto th…
Not long ago I saw a question in the VB Script forum that I thought would not take much time. You can read that question (Question ID  (http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/Q_28455246.html)28455246) Here (http…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

747 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

13 Experts available now in Live!

Get 1:1 Help Now