want to sort details by date - but the date field is TEXT!

[ekimr]

Hi,

How do I sort a detail group by date when the date is defined as TEXT.

if I have 3 fields 20/05/02 is always displayed as the higher value when my other dates are 02/06/02 and 09/06/02

I get either
20/05/02
09/06/02
02/06/02

OR
02/06/02
09/06/02
20/05/02

I guess because if you take the slashes out then numerically they are in ascending or descending order ...but not as dates!

Thanks

 

[rogar]

your text dates are apparently in dd/mm/yy format. You will either have to convert then to date types or reformat the strings to yy/mm/dd do sort then in correct date order. If all your dates have 8 characters (including the slashes, insert a formula, @SortDate:
Mid({datestring},7,2) & Mid({datestring},4,2) & left({datestring},2)
I have left out the slashes as they don't affect the sort order. Put the formula field on the detail line (suppressed) and sort on this formula instead of the date data field.

 

[Ngolem]

In order for dates to sort properly in text or numeric format they should be in a yyyymmdd format. A 2 digit year works as long as your data doesn't stray into the 1900's

you could compose a formula that would test for this and group or sort on this formula

@SortDate

if tonumber(right({table.dateStr},2)) > 30 then
"19" + right({table.dateStr},2) +
mid{table.dateStr},4,2) + left(({table.dateStr},2)
else
"20" + right({table.dateStr},2) +
mid{table.dateStr},4,2) + left(({table.dateStr},2);

This will work for at least 25 years


Jim Broadbent