Sort by terminal digit 2

[7wheel]

I'm using Crystal version 10.0 on an XP workstation. I have a report which lists patients' medical record numbers which are 6 digits in length.

I need to sort these patients using a medical record function called "sorting by terminal digit" which I understand is to sort by the last 2 digits; then by the middle 2 digits; then by the first 2 digits.

Does anyone know how this would be done?

Thank you in advance.

 

[synapsevampire]

Assuming they are numbers try:

(val(mid({table.field},5,2))*10000)+(val(mid({table.field},3,2))*100)+val(mid({table.field},1,2)))

Looks right, can't test right now.

-k

 

[7wheel]

K -
I didn't get the results I expected. To clarify...the report is grouped by physicians and each physician has 5 patients selected by random with their medical record number.

Your suggestion reversed the order of the med rec numbers; for example if original med rec no was 687899, the result was 997868.

I need to keep the original med rec as it is; but then sort the 5 patients under each physician sorted in terminal digit order e.g.
Med Rec # Sort result should be:
687899 756465
756465 245697
245697 687899

Appreciate any help or suggestions. Thanks

 

[chinjk]

Create following Formula -

numbervar n;
(cstr({table.number}[5 to 6]&{table.number}[3 to 4]&{table.number}[1 to 2]));


Then sort the data on that formula.

 

[GJParker]

7wheel

Use the formula field from SV's suggestion as the sort field but display the original med rec field on the report.

HTH

Gary Parker
MIS Data Analyst
Manchester, England

 

[7wheel]

This worked! Thanks to "chinjk" and "GParker" for their suggestions and exceptionally quick response.

This group is amazing!