How can I sort date when stored as text? 1

[mtl2302]

Crystal Reports XI
Oracle Database

How can I sort dates when they are stored as text in a formula.

I have a formula that sets a defined date range, say < 7/1/07 as text "Pre", the date range 7/1/07 - 7/31/07 as the actual date, and >7/31/07 as "Post".

I would like to sort the output in the following way:

Pre
7/1/07
7/2/07
...
7/30/07
7/31/07
Post

Naturally the dates don't sort correctly since they are stored as text.

Any ideas?

 

[dgillz]

Convert it to a real date with a formula, and sort on the formula.

Cdate({YourField})

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"If you have a big enough dictionary, just about everything is a word"
--Dave Barry

 

[mtl2302]

I've done all this, but here is the output:

Pre
7/15/2007
7/22/2007
7/29/2007
7/8/2007
8/12/2007
8/19/2007
8/26/2007
8/5/2007
9/2/2007
9/9/2007
Post

Does it matter that this is all in a cross tab report?

Here is the formula of which I am also sorting in the Record Sort Expert:

if {RTU_REPORTING_VIEW.WEEKCODE} < 27 then ' Pre'
else if {RTU_REPORTING_VIEW.WEEKCODE} > 36 then 'Post'
else totext(cdate({RTU_REPORTING_VIEW.CUR_TRN_DT}))

 

[lbass]

Change the formula to:

if {RTU_REPORTING_VIEW.WEEKCODE} < 27 then ' Pre'
else if {RTU_REPORTING_VIEW.WEEKCODE} > 36 then 'Post'
else totext(cdate({RTU_REPORTING_VIEW.CUR_TRN_DT}),"yyyy-MM-dd")

Then it should sort correctly. If you want it to display with your original format, then select the column heading (assuming this is a column)->right click->format field->common->display string and enter:

if currentfieldvalue <> [" Pre","Post"] then
{RTU_REPORTING_VIEW.CUR_TRN_DT} else
currentfieldvalue

-LB