asmyatt
asked on
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}+s
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})
else left({@LoanAmountFinal},th
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}
else right({@LoanAmountString},
4) formula LoanAmountString
if (IsNull({@Note_Amount_HMDA
else if {@Note_Amount_HMDA} < 1 then "NA"
else ToText({@Note_Amount_HMDA}
5) formula Note_Amount_HMDA
round({HMDA.Note $} * 0.001)
The error is on formula @LoanAmountFinal2 in the else statement
error: String length is less than 0 or not an integer
1) left({@LoanAmountFinal2}+s
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})
else left({@LoanAmountFinal},th
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}
else right({@LoanAmountString},
4) formula LoanAmountString
if (IsNull({@Note_Amount_HMDA
else if {@Note_Amount_HMDA} < 1 then "NA"
else ToText({@Note_Amount_HMDA}
5) formula Note_Amount_HMDA
round({HMDA.Note $} * 0.001)
vasto
check if @LoanAmountFinal2 returns null
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
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
ASKER
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.
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.
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
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
ASKER
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.