Sorting by number & text

[wanzek]

I have a report that uses a crosstab.

I have a formula that combines the employee number and the employee name. I would like to sort by the employee number but when I combine the number with the name it converts it to a string and it doesn't sort numerically. Any ideas?

Name Formula: {vrvPRCrewTimesheet.Employee} & ' '&{@Name}

 

[lbass]

Use:

totext({vrvPRCrewTimesheet.Employee},"00000000") & ' '&{@Name}

Add as many zeros as the maximum length of the employee ID.

-LB

 

[wanzek]

This works however it puts a zero in front of the employee ID's that are 4 digits instead of 5. Is there a way around that?

 

[SkipVought]

This works however it puts a zero in front of the employee ID's that are 4 digits instead of 5. Is there a way around that?

Maybe change the totext() mask to give you one more or less ZERO, I can't determine which from your statement?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[wanzek]

Our employee ID's range from 4 digits to 5 digits. For example the following employee ID's sort as follows with the formula:
ToText({vrvPRCrewTimesheet.Employee},"0000") & ' ' & {@Name}

1004
10637
1068
10680

With formula: ToText({vrvPRCrewTimesheet.Employee},"00000") & ' ' & {@Name}

01004
01068
10637
10680

This sorts correctly using the 2nd formula however I don't want the zero infront of the number but I want it to sort the same way.

 

[SkipVought]

This sorts correctly using the 2nd formula however I don't want the zero infront of the number but I want it to sort the same way

faq68-6659

I WANT to be 6'4, but it ain't gonna happen to the 5'8.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[fisheromacse]

use the formula you have with the leading zero to sort, and use your formula without the zero as the display.
(delete the default "group label" and replace with your formula)

 

[lbass]

If this is in a crosstab, use my formula for the row field, and then go to group options->options->customize group name->use a formula->x+2 and enter:

{vrvPRCrewTimesheet.Employee} & ' '&{@Name}

This way the sort will be correct, but the display will meet your requirements.

-LB

 

[wanzek]

It now sorts correctly using your fomula's Ibass. However I get the following:

1,004.00 Employee Name

How do I get rid of the decimals?

 

[lbass]

totext({vrvPRCrewTimesheet.Employee},0,"") & ' '&{@Name}

-LB

 

[wanzek]

THANK YOU!!!