Sort Problem 1

[kirbytheresa]

I am trying to group a report by a formula field. When my data is returned, that formula is sorted with alphabetics before characters like '-'. I am using a Sybase database.

If I do the sort on Sybase, the -'s are before alphabetics but since this is a formula field the sort is done on Crystal. My user wants the -'s first. Is there a way to fix this?

Example:

Crystal report shows '1993X04A' before '1993X04-'
User would like to see '1993X04-' before '1993X04A'

Thank you for any help!

 

[Naith]

I'm going to make the assumption that '-' is the universal suffix where the last character is neither a letter nor number. Perhaps you could try this out:

Change your grouping to this formula:

//@GroupOnMe
If Right({Field},1) = '-'
Then Replace({Field},'-','')
Else {Field}

But in the group header, you would display {Field} directly from the database - not @GroupOnMe. The idea is that this formula is used to group on, but is never displayed.

I think this should force values with hyphens at the end to mimic Sybase's order, but I'm afraid I haven't tested it. If you have loads of non-letter alpha characters to contend with, and '-' is just an example, then you'll have to specify to us what order you expect them to appear in.

Good luck with your report,

Naith

 

[kirbytheresa]

Naith,

Your fix worked.

My dashes are actual dashes and were in the middle of the field rather than at the end (my example showed them at the end). I changed the RIGHT to MID and changed the '' to ' ' so that the rest of the field could be sorted correctly and it fixed the problem.

Thanks again. This is the second time you've saved my programming career!

Have a great day...t

 

[dgillz]

Kirbyteresa,

I would think saving a programming career is worth AT LEAST one star. Software Training and Support for Macola, Crystal Reports and Goldmine
251-621-8972
dgilsdorf@mchsi.com

 

[kirbytheresa]

Good Point...thanks for taking care of it!..t