Crystal Specified Order

[Folkstone]

Hello,

I am creating a report through crystal version 8.5 and I am working with a string field {Disputes.Age_Catagory} which contains dates within it.

I am trying to extract the month and the year from this string field in order for me to create a specified grouping order. I want to see the following:

>11/04 (Debt previous to November - name 90+Days)
=11/04 (Debt in November - name 60 Days)
=12/04 (Debt in December - name 30 Days)

I have tried to do this within the grouping section by doing the following:

Specified Order:

90+ Days - less than 01/11/2004
60 Days - is between 01/11/2004 and 30/11/2004
30 Days - is between 01/12/2004 and 31/12/2004

I have also tried to only use the months (eg > 11/04) however the data is not being sorted into the correct groups.

Any ideas??
Thank you
Folkstone

 

[Katy44]

What exactly is not working? Are things going into the wrong groups? Are you getting extra data that doesn't fall into the categories?

 

[Madawc]

Just what formula are you using to extract the date from {Disputes.Age_Catagory}? And have you tried displaying it for each detail record?

Once you've got it as a valid date, grouping should be easy. If it isn't, please show what formula you are using to group.

Madawc Williams (East Anglia, UK)

 

[Folkstone]

Hello,

I have tried a few different options with this string field. I have firstly used:

CDate({Disputes.Age_Catagory})

This returned a "bad date format" error.

I then used the following formula:

Formula name: CDATE

stringvar strDate := {Disputes.Age_Catagory};
stringvar array arrDate := split(strDate,"/");
if ubound(arrDate)=3 then
cdate(totext(arrDate[1] & "/" & arrDate[2] & "/" & arrdate[3]))
else
cdate(0,0,0)

This worked however I only want to see the month and the year and this is showing it to me as dd/mm/yyyy. I then used the Month and Year formula:

Month({Cdate},0) & "/" & Year({Cdate},0)

This showed me: 3/2004

I tried to group by this:

90+ Days - is less than 11/2004
60 Days - equal to 11/2004
30 Days - equal to 12/2004

The 30 Days and the 60 Days showed correctly however the 90+ Days is not showing the correct data.

This is where I have gotten too.

Any ideas??

 

[Madawc]

You need to use date comparisons, stuff like
YourDate < Currentdate-90

Madawc Williams (East Anglia, UK)