Changing sort order of columns of different types

[Former Member]

I have created a formula on a subreport to change sorting based on variables.

select {?Pm-@Sorting}

case "property_type_desc ASC" :

{owned_property.property_type_desc}

case "property_type_desc DESC" :

{owned_property.property_type_desc}

case "item_no ASC" :

{owned_property.item_no}

case "item_no DESC" :

{owned_property.item_no}

default:

({owned_property.property_type_cd}; )

property_type_desc is a string and item_no is a number. If I use "CStr" on item_no, it doesn't sort correct (i.e. putting "100" ahead of "2"). How can I do this?

Secondly, how can I change the sort order ascending and descending using the same formula??

Thanks, Jessica

[Former Member · Accepted solution]

I've run into the same problem trying to do parameter based sorting on both character and numeric fields. I was able to resolve by using "totext" and specifying the format:

totext({owned_property.item_no},"000")

That will covert your "2" to "002". Just be sure to add enough "0"s in the format mask to cover the max length of your field.

You can also use the same technique for date fields, totext(,"yyyy-MM-dd")

[Former Member · Accepted solution]

Thanks. That worked great on the different types.

Any ideas on how to change the ascending/descending dynamically?

[Former Member · Accepted solution]

I'm going to create another thread to handle the sorting issue.

[Former Member · Accepted solution]

Never tried this, but here's an idea...

If you are only sorting on 1 field, maybe you could actually set-up 2 sort fields, one asc and one desc. In your formula, if a asc selection is chosen feed that into sort 1, if a desc selection is chosen, feed that into sort 2 and feed null into sort 1. Something like...

SortOrder1

if ="AscendingChoice1" then else if ="AscendingChoice2" then

else if ="DescChoice1" then "" else if ="DescChoice2" then ""

SortOrder2

if ="AscendingChoice1" then "" else if ="AscendingChoice2" then ""

else if ="DescChoice1" then else if ="DescChoice2" then

then using the sort expert, set @SortOrder1 as the 1st sort, sorting Asc and set @SortOrder2 as the 2nd sort, sorting Desc