Replace Record Selection Formula with useful text 1

[paulharman1]

I have a report which the users (via an app) select a date range. Im trying to get that selection to display on the report.

I have added the special field 'Record Selection Formula' but that returns

{wmTransaction.RecordStatus} = 2 and{wmTransaction.FirstDate} in DateTime (2011, 02, 16, 0, 0, 0) to DateTime (2011, 02, 21, 0, 0, 0)

The date will always be 0, 0, 0 referenced (ie just the date, no time). And we dont need to know about the recordstatus. So from that text I would ideally want

"2011-02-16 to 2011-02-21"

Can anyone help please?

 

[Madawc]

Try Report > Selection Formulas > Records, that lets you edit the text.

It helps to give your Crystal version - 8, 8.5, 9, 10, 11 or whatever. Methods sometimes change between versions, and higher versions have extra options. In this case, it probably makes no difference.


Madawc Williams (East Anglia, UK). Using Crystal 11.5 with SQL and Windows XP

 

[paulharman1]

Thanks for your reply.

Does this edit the selection criteria itself, or just the text as it is displayed on the report?

If I were to remove and edit that, would it apply the mask to any changes made to the selection criteria?

In case I havent explained it too well, the user selects their selection criteria (which is a 'between' on a date field). I want a header section to display the selected date range at the top of the report.

I am using v10 btw.

 

[Madawc]

It changes the selection criteria. You will also find some of them under Database > Show SQL Query (assuming you are using SQL.)

Madawc Williams (East Anglia, UK). Using Crystal 11.5 with SQL and Windows XP

 

[paulharman1]

Hi,

I dont need to change the selection criteria, I want to display the selection criteria on the report, in a certain format.

Thanks

 

[lbass]

The select expert is for use by the report designer. If the dates will remain the same, then you can just hard code them into a formula for display.

If they will change, then it would be better to create parameters for start date and end date. Then you can just reference the parameters in your display formula as in:

totext({?startdate},"MM/dd/yyyy") + " to " + totext({?enddate},"MM/dd/yyyy")

-LB

 

[paulharman1]

Unfortunately the application which runs the reports using the selection criteria as the parameters so I am unable to add them to the report.

If I add the special field Record Selection Formula to the report, this shows the criteria used, but I would like to format it in a nicer way.

Is this possible? If not, how would I return the date of the first record and the date of the last record (sorted by date obviously) in order to get my start and end dates?

Thanks

 

[lbass]

Create this formula (exactly):

stringvar array x := split(recordselection,"datetime");
numbervar i;
numbervar j := ubound(x);
stringvar array y;
for i := 1 to j do(
redim preserve x[j];
redim preserve y[j];
y := extractstring(x,"(",")")
;
totext(date(y[2]),"MM/dd/yyyy")+" to "+
totext(date(y[3]),"MM/dd/yyyy")

-LB

 

[paulharman1]

Thanks lbass, but I get a message

The ) is missing

If I add an ) to the end, I then get an error

A subscript must be between 1 and the size of the array

 

[lbass]

stringvar array x := split(recordselection,"datetime");
numbervar i;
numbervar j := ubound(x);
stringvar array y;
for i := 1 to j do(
redim preserve x[j];
redim preserve y[j];
y := extractstring(x,"(",")")
); //add the paren here
totext(date(y[2]),"MM/dd/yyyy")+" to "+
totext(date(y[3]),"MM/dd/yyyy")

-LB

 

[paulharman1]

Sorry but I am still getting

A subscript must be between 1 and the size of the array

 

[lbass]

Please copy your formula into the thread.

-LB

 

[paulharman1]

Hmm looking at it, what do you mean by

//add the paren here

Whats the paren? Im a total crystal newbie Im afraid.

 

[paulharman1]

oh you mean parenthesis? bracket?

 

[paulharman1]

In which case Im using

stringvar array x := split(recordselection,"datetime");
numbervar i;
numbervar j := ubound(x);
stringvar array y;
for i := 1 to j do(
redim preserve x[j];
redim preserve y[j];
y := extractstring(x,"(",")")
); )
totext(date(y[2]),"MM/dd/yyyy")+" to "+
totext(date(y[3]),"MM/dd/yyyy")

but get the error highlighted on

)
totext(date(y[2]),"MM/dd/yyyy")+" to "+
totext(date(y[3]),"MM/dd/yyyy")

which says The remaining text does not appear to be part of the formula

 

[lbass]

stringvar array x := split(recordselection,"datetime");
numbervar i;numbervar j := ubound(x);
stringvar array y;
for i := 1 to j do(
redim preserve x[j];
redim preserve y[j];
y := extractstring(x,"(",")")
);
totext(date(y[2]),"MM/dd/yyyy")+" to "+
totext(date(y[3]),"MM/dd/yyyy")

-LB

 

[paulharman1]

Im still getting A subscript must be between 1 and the size of the array

 

[lbass]

Please copy the record selection formula into this thread and I will test it here with the formula.

-LB

 

[paulharman1]

{wmTransaction.RecordStatus} = 2 and
{wmTransaction.FirstDate} in DateTime (2011, 02, 16, 0, 0, 0) to DateTime (2011, 02, 21, 0, 0, 0)

Thanks

 

[lbass]

Okay, the following works when tested here. Please copy this into your formula area--even the case on DateTime seemed to matter.

stringvar array x := split(recordselection,"DateTime");
numbervar i;
numbervar j := ubound(x);
stringvar array y;
for i := 1 to j do(
redim preserve x[j];
redim preserve y[j];
y := extractstring(x,"(",")")
);
totext(date(val(left(trim(y[2]),4)),val(mid(trim(y[2]),7,2)), val(mid(trim(y[2]),11,2))),"MM/dd/yyyy")+" to "+
totext(date(val(left(trim(y[3]),4)),val(mid(trim(y[3]),7,2)), val(mid(trim(y[3]),11,2))),"MM/dd/yyyy")

-LB