Multiple Sort Criteria - formula error

[stcholly]

I'm trying to write a report with multiple sort options. It is telling me that the default: "" needs to be a date field. If I put a date field then it tells me the last three lines need to be a number. What is the proper fix?

//Output 1st sorting criteria
if count({?Sort Conditions})>=1 then
(
select {?Sort Conditions}[1]
case "Job": {MASTER_JCM_JOB.Job}
case "State": {MASTER_JCM_JOB.State}
case "PM": {MASTER_JCM_JOB.Project_Manager}
case "Super": {MASTER_JCM_JOB.Superintendent}
case "Contract Amount": {MASTER_JCM_JOB.Revised_Contract_Amount}
case "Size": {MASTER_JCM_JOB.Size}
case "Start Date": {MASTER_JCM_JOB.Actual_Start_Date}
case "Complete Date": {MASTER_JCM_JOB.Actual_Complete_Date}
default:""
)

Thanks!

 

[lbass]

The potential results of a select case statement must be of the same datatype, so try converting each result to a string, e.g.,

totext({MASTER_JCM_JOB.Actual_Start_Date},"MM/dd/yyyy")

...or

totext({MASTER_JCM_JOB.Revised_Contract_Amount},2)

-LB

 

[Charliy]

except you probably want the dates to be:
Totext({table.field},"yyyyMMdd")

 

[stcholly]

I was able to get the "mm/dd/yyyy" to sort correctly.

My only adjustment to the lbass's reply was the dollar amount sorted by the first digit only. I was able to fix by convering the
totext({MASTER_JCM_JOB.Revised_Contract_Amount},2)
to
totext({MASTER_JCM_JOB.Revised_Contract_Amount},"00,000,000.00")

Not sure if there was an easier/better fix, but it worked.

Thank you both for your replies!

 

[lbass]

Both were good catches and the correct solutions. You really should sort by "yyyyMMdd"--you don't have to display the results this way, and to sort by an amount or currency, the solution is to format for the maximum number of digits, as was done here. Sorry I didn't actually provide the correct solution in the first place!

-LB