Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Multiple Sort Criteria - formula error

Status
Not open for further replies.

stcholly

Technical User
May 17, 2006
25
US
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!
 
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

 
except you probably want the dates to be:
Totext({table.field},"yyyyMMdd")
 
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!
 
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top