How can I sort by date in a subreport? CR11

[adoctor]

Hello-

I'm new to Crystal Reports 11 and have figured out lots of formulas, but I can't seem to get this sorting problem solved. Here's the issue:

In a subreport that pulls bibliography records, I want to sort the records by the displayed date. The dates are displayed in different formats depending on the information available: YYYY, MMMM-YYYY or DD-MMMM-YYYY.

I have tried using the Sort Expert but there is always one or two dates still out of order. I see that there is a way to sort using a formula, but I am unsure as to how to write that formula.

Can you help? Did I give enough information?

Thanks,

Angela Doctor

 

[Turkbear]

Hi,
Use the formula to format the date so that it will sort the way you want:
ToText({datefield},'yyyy-MM-dd'), for instance..

This assumes that the field is actually a Date type ( what database, by the way?)





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[synapsevampire]

If it is a date type, it will sort correctly.

So what you have are likely string fields which are operating as dates, which can be indicative of poor database design, in either case the best solution is likely to convert them to dates and then use a formula to display them however you need, but leave the data types as dates (or datetimes).

You might use a SQL Expression on the database side (please include basic technical information with your posts), or use a formula to create a real date from the field.

-k

 

[adoctor]

We use The Museum System which uses MS Access. I'm not totally literate yet with explaining myself, so many apologies to you as I slog through this...

The Display Date field that I want to sort by is a text field. There are Beginnning and End Date fields that are date fields but only contain YYYY. What kind of technical info can I give you in order to help myself?


Thanks, A

 

[synapsevampire]

Ahh, right, that was fine info.

What you have are year fields stored as text, not date fields.

The year fields will sort correctly, I suspect that the MMMM-YYYY or DD-MMMM-YYYY are where you are having difficulties. Giving example data is always a better idea than trying to describe it, MMMM-YYYY sounds like you have the alpha months involved at four characters each? We don't know what you're working with, we need clarification.

In general you need to make numerics out of the month and the year parts, or make dates.

Please post an example of the problem dates.

-k

 

[adoctor]

Yes! The years are all in correct order no matter what other data is there.

So, I have experimented with using the Sort Expert. I tried sorting by Display Date, then I tried sorting by Begin Date, which came close, and is probably the reason why the years always sort correctly. Here is a list of dates from my report in the order the appear using the Sort Expert on Begin Date. THey are all in good order EXCEPT when it gets to April and August 1977:

17 March 1958, 65
(November 1965)
(November 1965)
(January 1966)
(February 1969)
(New York, 1973)
(Buffalo, 1976)
5 December 1976
(1977)
17 April 1977
27 August 1977
16 April 1977
5 February 1977
September 1981
(San Diego, CA, 1984)
8 December 1997
23 October 1997
9 October 1997

 

[synapsevampire]

Yeah, we need to make real dates.

I don't understand why you have city names and years though.

It would have been best to state for this field here's examples:

...

And for this field here are examples:

...

And state why you have cities involved, and how to handle them...

So here's a formula for you:

whileprintingrecords;
stringvar array MyDate:= join({table.mmmm-yyyy field}," ");
datevar OutDate;
numbervar MyMonth;
if ubound(MyDate) = 3 then
(
if MyDate[2] = "January" then
MyMonth:=1
else
if MyDate[2] = "February" then
MyMonth:=2
else
if MyDate[2] = "March" then
MyMonth:=3
else
... you get the idea, finish this ...
);
if ubound(MyDate) = 3 then
OutDate:= cdate(val(MyDate[3]),MyMonth,val(MyDate[1]);
Outdate

This should work on the dates with 3 values.

Again, try posting specifics if you want specific help.

-k

 

[adoctor]

Good morning-

So, the city names are for certain types of publications in bibliography records. We use the correct book publishing formats for date references in our report. One of those requires us to state the city or city/ST of publication and year. We would want to sort these by year first then city name.

I am trying the formula you sent and have some more questions. I get an error message asking me for the correct field name for this bit: stringvar array MyDate:= join({table.mmmm-yyyy field}," ");

When I replace {table.mmmm-yyyy field} with {ReferenceMaster.DisplayDate} it tells my that a string array is required.

Also, can you tell me the next step after I get this formula error-free?

THanks, Angie

 

[adoctor]

Sorry. Here's the formula I am referring to:

whileprintingrecords;
stringvar array MyDate:= join({ReferenceMaster.DisplayDate}," ");
datevar OutDate;
numbervar MyMonth;
if ubound(MyDate) = 3 then
(if MyDate[2] = "January" then MyMonth:=1 else
if MyDate[2] = "February" then MyMonth:=2 else
if MyDate[2] = "March" then MyMonth:=3 else
if MyDate[2] = "April" then MyMonth:=4 else
if MyDate[2] = "May" then MyMonth:=5 else
if MyDate[2] = "June" then MyMonth:=6 else
if MyDate[2] = "July" then MyMonth:=7 else
if MyDate[2] = "August" then MyMonth:=8 else
if MyDate[2] = "September" then MyMonth:=9 else
if MyDate[2] = "October" then MyMonth:=10 else
if MyDate[2] = "November" then MyMonth:=11 else
if MyDate[2] = "December" then MyMonth:=12 else '');
if ubound(MyDate) = 3 then
OutDate:= cdate(val(MyDate[3]),MyMonth,val(MyDate[1]));
Outdate

 

[adoctor]

Hi out there-

Can anyone help me with this? Much appreciation...

Angie