Changing sort order of columns of different types
I have created a formula on a subreport to change sorting based on variables.
case "property_type_desc ASC" :
case "property_type_desc DESC" :
case "item_no ASC" :
case "item_no DESC" :
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??
Deborah Tolley replied
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:
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")