String to Number?

[ddnh]

Is there a way to convert a field with a STRING data type to a number for sorting purposes?

I need to sort ID numbers in ascending order. The problem is, the field is a string so I get the following when I sort:

10411
9028
9029
9696
9830

I want it to sort like this:

9028
9029
9696
9830
10411

 

[IdoMillet]

Use the ToNumber() function.

hth,
- Ido

CUT, Visual CUT, and DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[ddnh]

I was just coming back to post that myself. I was trying ToNum before and didn't realize it was ToNumber.

 

[ddnh]

Is there a way to format the new number in the formula to remove decimals?

Here is my formula:

If NumericText ({SorDetail.MStockCode}) Then
ToNumber ({SorDetail.MStockCode})
Else 0


I can format the field once it's on the report if I right click\format field\customize\then change decimals to 1. This removes the decimal on the report itself, but in the group tree, it still has decimals. I then went to File\Options\Fields\Number\Number\Customize and changed the decimals to 1. The number now displays properly in the on the report and in group tree if I have the report open in CR. BUT, if I open the report in ePortfolio Lite....the numbers in the group tree still have extra decimal places. Any suggestions on how to remove them?

ex:
in CR the number shows as 9028 in the group tree and on the report.

when viewing the report in ePortfolio Lite, the number shows as 9028.00

 

[synapsevampire]

Numbers default to the format that you have set in the File->Options->Fields->Number

However CE should honor any formatting that overrides this in the report directly, so it sounds like a buglet.

Perhaps if you change the format to -1123 or -1,123 CE will preserve it.

You might also try this kludge:

If NumericText ({SorDetail.MStockCode}) Then
val(totext(val({SorDetail.MStockCode}),0,""))
Else
val(totext("0",0,""))

-k

 

[ddnh]

I guess we'll have to chalk this one up to "bug."

I tried all of your suggestions...all had the same result. No decimals appear in the group tree or on the report in CR, but the group tree has the .00 in CE.

Oh well. I'm upgrading from 9 to 10 next week. Maybe the new install will fix the problem.

Thanks.

 

[synapsevampire]

Did you try the service pack for CE?

-k

 

[synapsevampire]

OK, I found a kb article on this, the version number helped:

http://support.businessobjects.com/library/kbase/articles/c2013748.asp

-k

 

[ddnh]

thanks, I'm sure that will help. I can't try it yet though since I created the report WITH a printer, but don't have a 'Crystal Configuration Manager.' Apparently our install is incorrect...one of the reasons we're upgrading.

 

[synapsevampire]

Ahhh, a bad install is easy to fix, format C:

-k

 

[IdoMillet]

Instead of relying on Crystal to format the number as you expect, create a string formula:

ToText(ToNumber({Your_Text_Field}, "0000#")

Your numbers:
9028
9029
9696
9830
10411

Would become
09028
09029
09696
09830
10411

Them group on that formula, instead of on the original text field.

Cheers,
- Ido

CUT, Visual CUT, and DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[crystalreporting]

Always include an if statement on the ToNumber function just in case the field ever ends up with non-numeric data. I like to do the following;

if NumericText(table.field)
then
ToText(ToNumber(table.field),0,"")
else
table.field

This statement converts numeric fields to a 'whole' number (no decimals = 0, no thousands separator = ""). If the field happens to contain a non-numeric field, then you see that too. This is also a great way to remove leading zeroes.

Peter Shirley

http://www.linkcorp.com