Print Page | Close Window

the string is non-numeric

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Report Design
Forum Discription: The best way to design a report and problems you have encountered
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=13610
Printed Date: 06 May 2024 at 1:30am


Topic: the string is non-numeric
Posted By: chudok
Subject: the string is non-numeric
Date Posted: 27 Jun 2011 at 9:13am
I have the following formula.  I am having to upgrade our MRP system and one of my custom reports has the following error in this code (the red highlighted section:  the string is non-numeric - how do I put in a if null then 0 statement to this?


NumberVar  CAT4;
IF {SY0_CompanyParameters.ARAgeByDayOrMonth} = "D" Then
    NumberVar  CAT4:=ToNumber({SY0_CompanyParameters.ARAgingCategory4})
Else     NumberVar CAT4:=120 ;

IF {@agedate}-{@InvOrDueDate} >= NumberVar CAT4 THEN
{AR_OpenInvoice.Balance}
ELSE
0.00

I've tried    

NumberVar  CAT4:=if (isnull({SY0_CompanyParameters.ARAgingCategory4}))then 0 else(ToNumber({SY0_CompanyParameters.ARAgingCategory4}))
Else     NumberVar CAT4:=120 ;

still get the same error


Replies:
Posted By: DBlank
Date Posted: 27 Jun 2011 at 9:24am
more likely you have a value that is not numeric rather than null
try a select statement to find which row it is choking on
 

Posted By: chudok
Date Posted: 27 Jun 2011 at 9:26am
I am not sure how to do that.... can you help direct me in the right direction?  It's been a while since I've used Crystal and I am a little rusty......

Posted By: DBlank
Date Posted: 27 Jun 2011 at 9:29am
in the select expert try
NOT(isnumeric({SY0_CompanyParameters.ARAgingCategory4}))
 

Posted By: chudok
Date Posted: 27 Jun 2011 at 9:51am
All I get back are blanks(null?).  When I browse the field data it comes back as String Type Length: 3

Not sure where to go from here.....

Posted By: DBlank
Date Posted: 27 Jun 2011 at 9:54am
in that case, in the formula editor do you have a pick list option in the expression editor to re: NULLS?
Set it to "Default values for Nulls'.

Posted By: chudok
Date Posted: 27 Jun 2011 at 10:01am
Did that - same problem still "The string is non-numeric"

Leave it at Crystal Syntax too?

Posted By: DBlank
Date Posted: 27 Jun 2011 at 10:08am
can you query the DB directly?

Posted By: chudok
Date Posted: 27 Jun 2011 at 10:13am
The only tool I currently have to this database is Crystal.  IT does not even have excel out there where I can use that to query the db. I do not have access to SQL either.....

If I can get IT to create a connection to excel will that work for what you are looking for me to do?

Posted By: DBlank
Date Posted: 27 Jun 2011 at 10:15am

In the select expert try this

isnull({SY0_CompanyParameters.ARAgingCategory4})
 
do you get the same amount of records as
NOT(isnumeric({SY0_CompanyParameters.ARAgingCategory4}))
 

Posted By: chudok
Date Posted: 27 Jun 2011 at 10:21am
NOT(isnumeric({SY0_CompanyParameters.ARAgingCategory4})) - I get 1668 records back

isnull({SY0_CompanyParameters.ARAgingCategory4}) I get 0 records back

so that is telling me that they are not null then.  So would the best thing be to clear out the spaces on the db? 

Posted By: DBlank
Date Posted: 27 Jun 2011 at 10:29am
just add in the condition to look for an empty string and make it 0 (which will not convert to a 0 like a NULL does).
 
IF {SY0_CompanyParameters.ARAgeByDayOrMonth} <> "D" Then 120 else if trim({SY0_CompanyParameters.ARAgingCategory4}))="" then 0 else tonumber({SY0_CompanyParameters.ARAgingCategory4})


Print Page | Close Window