Doing Sums on Field that was a String field but was converted to a number field

Joanne Felci 2

I'm in the middle of recreating a bunch of Crystal Reports that someone else created about 5 or more years ago.


I'm basing them on CSV files (she had them as Excel 97-2003 format which I think is why they all broke) and I have a field that we use as a number but it is coming through as a string


I've used the ToNumber 'formula' to change it to a number but when I try to sum on the formular I'm not getting correct results


Help!


Also if anyone has any good online help sites for formulas, running total formulas, etc. that would be great.  I'm doing google searches but it is only helping so much.

Marie Stark

A good website is
http://kenhamady.com/formulas/default.html

 

 

Joanne Felci 2

Thanks Marie!

James Andrews

Joanne Felci:

I'm in the middle of recreating a bunch of Crystal Reports that someone else created about 5 or more years ago.


I'm basing them on CSV files (she had them as Excel 97-2003 format which I think is why they all broke) and I have a field that we use as a number but it is coming through as a string


I've used the ToNumber 'formula' to change it to a number but when I try to sum on the formular I'm not getting correct results


Help!


Also if anyone has any good online help sites for formulas, running total formulas, etc. that would be great.  I'm doing google searches but it is only helping so much.

Is it possible you have non-numeric data in the field? Or that there are nulls? 


Actually if you have non-numeric data you should be getting an error saying so, so I'd bet it's nulls screwing things up. You could try something like:


if Isnull({field}) then 0 else tonumber({field})


and see if that makes a difference? 

Joanne Felci 2

Thanks James!


It is a string field (or coming in that way) though it is really numeric.  I was able to solve it using a tonumber formula and then figured out how to do a running total based on that formula.


We also figured out it was one of 170 Office updates that were pushed out to our PCs last week that created all this mess.


I hope/pray that recreating these reports and connecting them to a CSV (instead of the Excel 97-2003 file type they were all previously associated with) will prevent this from happening in the future.


And I'm getting to learn new stuff, and my day is going by quickly - yahoo!