Hi,
 

I have created a custom sort field to my report as shown below. 
The idea is if sort string is null I want to sort by work date and time card index else I want to sort by work date and sort string

If IsNull ({ListofProfDetailTime.PresTimekeeper1__TkprDate__Title1__SortString}) then
    toText({ListofProfDetailTime.WorkDate}) & ToText({ListofProfDetailTime.Timecard1__OrigTimeIndex})
else
    toText({ListofProfDetailTime.WorkDate}) & ToText({ListofProfDetailTime.SortString})

 

However when the report is displaying the order of the work date is wrong
 

The work date is coming out something like :
 

07/10/2013

08/09/2013

09/10/2013

10/10/2013

22/09/2013

Month 09 is after 10. 

Can someone please help me to solve this.

Thank you Sastry.  This works.  but I am confuse now.  Does that mean the sort order is ignoring my formula.  Because I need the sort order by both work date and timeIndex if sort string is null or sort by both work date and sort string if there is a sort string ?

By adding the work date in the record sort expert it is ignoring my formula totally and just sorting it by work date.  Whereas I need to sort the report by both work date + timeIndex or Work Date + Sort string.

you double posted this, and Sastry is basically giving the same solution as me and DBlank. Just a slightly different twist.

as another solution...Sastry's explanation tripped this thought, how about changing your formula to someting like
local numbervar tot
tot := year({table.workdate}) * 100 * 100 * 10;
tot := tot + month({table.workdate}) * 100 * 10;
tot := tot + day({table.workdate}) * 10;
tot := tot + {ListofProfDetailTime.Timecard1__OrigTimeIndex}; //or the other field

tot

then you can sort by the formula as everything will be 1 number.

you would probably want to group by the workdate, and order by the formula...but that might be more work than just having 2 groups.

Grouping on the above formula would be silly