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]

lBass:

Thanks once again. I have to say I'm a bit surprised by the returned value from val. One might think that negative and dollar signs would have been taken into account. Still it's a handy function to have around.