SORTING ALPHANUMERIC FIELD VALUES 2

[sonper]

Hi guys! I'm currently working on a project which requires me to sort alphanumeric values. Examples are 101A, 101B 1A, 1B, 1C, 2A, 109A, 100A and so on ......

When I sort all these field values using the Crystal sort record command on the Report Menu, I couldn't seem to get the right order which is:

1A,1B,1C,2A,100A,101A,101B,109A

Can anyone here please help! Thanks.

I'm using Crystal Report 7 and MS Access.

 

[lbass]

You could try using the following formula in report->sort order:

totext(val({table.field}),"000&quot+right({table.field},1)

You could use the field itself for display in the report.

-LB

 

[sonper]

lbass,

Thank you for your reply. I used the formula but I'm getting an error which says: "The result of selection formula must be boolean."

Any idea? What does it implies?
Thanks again.

 

[lbass]

You should be creating this formula in the formula editor.
Insert->field explorer->formula->new. Then this should appear as an option in the list of fields for sort order--report->sort records.

-LB

 

[synapsevampire]

You may have to expand the number of zeroes in the formula to allow for longer strings, or you can use 2 formulas to sort on:

First sort on
@numberpart
val({table.field})

Then sort on
@alphapart
evaluateafter({@numberpart});
replace({table.field},totext({@numberpart},0,"&quot,"&quot

-k

 

[sonper]

lbass,
Thanks again. I got your code working but is there a way I can get rid of the zeroes in such a way that 001A becomes 1A. Please bear with me sir, I been working on this code for almost a week now and my boss it getting mad already. Thanks.


synapsevampire,
Thanks also for your reply. I tried the code but I'm getting an error "The remaining text does not appear to be part of the formula" and also I can't find the function REPLACE on the function list. Thanks.

 

[lbass]

Use the formula as the sort field, but use the original field in the report itself. If you need to group based on the alphanumeric field, use the formula for grouping, but then delete the group name and use the field itself for display purposes. In other words, the formula results do not have to display anywhere on your report, although they are being used for sorting or grouping.

-LB

 

[sonper]

lbass,
Thank you so much. I couldn't have done this without your help. This is one thing I like with tek-tips, it's not only a community of good and excellent people but also a community of people with kind hearts able and willing to help neos like me. You're great! Thanks mucho! God bless.

Percy