Sorting of a currency field incorrect when values are negative

[ARRao]

I have a parameter @SortField that allows users to pick the field they want to sort on. The fields could be of a string, number,date or currency data type.

The sort works perfectly fine for all datatypes except for currency.

I have gone through the post by vshapiro but that did not help.

Here is the solution I tried:
if ({sp_spname;1.FieldName} >= 0 then
totext({sp_spname;1.FieldName},"000000000.00")
else
"#" + totext(1/val(ToText(({sp_spname;1.FieldName}2,"")),6,"");

I get a "Division By Zero" Error. On furthur investigation, it looks like the Val function returns 0 for all negative values.

Any help would be greatly appreciated. Thx ..ARRao

 

[beanbrain]

Interestingly Help says nothing about Val doing with with negative number, however, you might try "abs" with "val" instead. Will {sp_spname;1.FieldName} always be a number?

 

[ARRao]

Thanks for the suggestion but the abs function requires a number. {sp_spname;1.FieldName} is of Currency Data type.

 

[kskid]

What do you want to do if {sp_spname;1.FieldName} is null?

-LW

 

[ARRao]

I am currently not handling nulls but they show up as 0.00 in the report. You think that could be the problem?
If so, I can convert all the nulls to 0 within my stored proc.

 

[kskid]

Try this

if isnull({sp_spname;1.FieldName}) or
{sp_spname;1.FieldName} = 0 then
  "00000000.00"
else if
 ({sp_spname;1.FieldName} >= 0 then
  totext({sp_spname;1.FieldName},"000000000.00")
else
  "#" + totext(1/val(ToText(({sp_spname;1.FieldName}2,"")),6,"");

-LW

 

[beanbrain]

This still won't handle negative numbers which val will convert to zero. Yes, I tried it.

We do not use any currency or money data fields in any of the tables so I'm not sure if this will work, but considering that currency is a number field type it seems that it should work;

if isnull({sp_spname;1.FieldName}) or
{sp_spname;1.FieldName} = 0 then
  "00000000.00"
else if
 ({sp_spname;1.FieldName} >= 0 then
  totext({sp_spname;1.FieldName},"000000000.00")
else
  "#" +  "#" + totext(1/val(ToText(abs({sp_spname;1.FieldName}),2,"")),6,"");

<i>A comma was missing before the two</i>
I might be completely wrong about this but I think it's worth a try.

 

[ARRao]

Tried the last code posted. Still get the Division By Zero error.

 

[beanbrain]

Oops. Just noticed two "#" + in the last else area.

Works for me, by the way! I did try a stripped down version of this formula and got the "Divide by Zero" error you mentioned. This had to do with negative numbers returning 0 with val.

You might want to try displaying the contents of field {sp_spname;1.FieldName} in a detail section just to see whats coming back. You might also try val(toText({sp_spname;1.FieldName})) again to see what's being returned.

if isnull({sp_spname;1.FieldName}) or
{sp_spname;1.FieldName} = 0 then
  "00000000.00"
else if
 ({sp_spname;1.FieldName} > 0 then
  totext({sp_spname;1.FieldName},"000000000.00")
else
  "#" +  totext(1/val(ToText(abs({sp_spname;1.FieldName}),2,"")),6,"");

 

[beanbrain]

One more correction to remove errant open paren in "else if."

if isnull({sp_spname;1.FieldName}) or
{sp_spname;1.FieldName} = 0 then
  "00000000.00"
else if
 {sp_spname;1.FieldName} > 0 then
  totext({sp_spname;1.FieldName},"000000000.00")
else
  "#" +  totext(1/val(ToText(abs({sp_spname;1.FieldName}),2,"")),6,"");

 

[ARRao]

Thank you, but I still get the error.
I do display the {sp_spname;1.FieldName} in the report and they show up as currencies.
The negatives show up as ($180.33) etc.
When i strip the function and place val(toText({sp_spname;1.FieldName})) in another formula, i get the same error.

 

[beanbrain]

ARRao:

Well I know from trying this myself that whenever Val "sees" a "-" or "(" it returns a zero.

What happens when you try;

val(toText(toNumber({sp_spname;1.FieldName})))

 

[beanbrain]

If that works then you would want;

 if isnull({sp_spname;1.FieldName}) or
{ASR_DETAILS.TOTAL_BUDGET} = 0 then
  "00000000.00"
else if
 {sp_spname;1.FieldName} > 0 then
  totext({sp_spname;1.FieldName},"000000000.00")
else
  "#" +  totext(1/val(ToText(abs(toNumber({sp_spname;1.FieldName})),2,"")),6,"");

 

[beanbrain]

Darn it! Please correct the field {ASR_DETAILS.TOTAL_BUDGET} to {sp_spname;1.FieldName} before doing this.

 

[ARRao]

That worked beautifully. Thank you!!!!

 

[beanbrain]

There had to be a way B-)

 

[lbass]

Please note that val works with negative numeric strings, e.g., val("-9") would return -9.00. I think that what was occurring here was that val was encountering a currency sign as the first character, and therefore returning 0.00. Probably:

val(totext(tonumber({table.currency})))

...would have worked.

-LB

 

[ARRao]

Thank you all!!

 

[beanbrain]

LBass:

Good to know. When I used val with numbers that returned negavies as such as (1.00) the returned value was zero. So perhaps any non-numeric character run through val will return zero.

 

[lbass]

It might be the use of parens to signify a negative that is the problem. The val function works as long as there isn't a leading non-numeric character. So, for example, val("9J") would return 9.00, but val("J9") would return 0.00.

-LB