Greetings, 

 

I'm trying to sort numerically & alphabetically on the Title field
(string field). The data we store in this field may contain number
strings, letter strings, or a combo of both. (ex: 145, O4, QE, PN234)
Sorting on the Title field will not yield a true numerical sort, instead
I get  12, 120, 121, 13, etc.

 If I convert the string to a numeric value using the Val() function in
Crystal, my alpha character values drop off the report.  What I need is
a formula that converts the characters beginning with numbers to a
numerical value, and the leaves the rest to sort as alpha characters.
I've taken a stab below but it doesn't work:

 

if left ( {ObjTitles.Title}, 1) in "1" to "9"

then Val({ObjTitles.Title}) 

else {ObjTitles.Title}

 

Or, there may be a simple function in crystal that I am unaware of?

 

Thank you Crystal gurus!

-Kate

 

Kate Diggle

Collections Database Administrator

Smithsonian National Postal Museum

202.633.5512

www.postalmuseum.si.edu

www.arago.si.edu