Record selection based on date from parameter 2

[dh42891]

I'm trying to select records based on a date from a parameter. I want a handful of default values available, but they must be relative so I don't think I can use a date parameter. I've set up a string parameter with default values that allow me to set up a record selection formula that lets me define what date each default string values return. Example of working values:

for string parameter {?Date Range} default values:
"Today"
"Tomorrow"

record selectin formula:
if {?Date Range} = "Today" then
{Table.Date} = CurrentDate
else if
{Date Range} = "Tomorrow" then
{Table.Date} = CurrentDate + 1

What I want to be able to do is set up a handful of these default values, but also allow the user to input a single date or a range of dates, but I don't know how to do that.

I thought about making another default value "Other" that would call another parameter. That second parameter would actually be a date. But I don't really know how to do that either.

Any ideas?

thanks

dylan

 

[bdreed35]

You are on the right track.

Here is an example using a string parameter with several pre-set options, and then an option for Other, that would allow the user to specify a date.

Add these options as the default values:

Yesterday
Today
Tomorrow
Other

Create a date parameter, name it Run Date.

Now, in your selection criteria add the following code:

{table.date} in
switch
(
    {?Date Range} = "Yesterday", currentdate - 1,
    {?Date Range} = "Today", currentdate,
    {?Date Range} = "Tomorrow", currentdate + 1,
    {?Date Range} = "Other", {?Run Date}
)

This can be modified for whatever period you need. If you need help with that, just post the periods you need,

~Brian

 

[dh42891]

CR doesn't like

{?Date Range} = "Other", {?Report Date Range}

tells me: "This array must be subscripted. For example Array ."

Here is my exact record selection code to clarify:

{TurnstileStatistics.TSS_ActivityDate} in
switch
(
    {?Date Range} = "Today", currentdate,
    {?Date Range} = "Tomorrow", currentdate - 1,
    {?Date Range} = "One Week Forward From Today", currentdate to currentdate + 7,
    {?Date Range} = "One Week Forward From Tomorrow", currentdate + 1 to currentdate + 8,
    {?Date Range} = "Next 30 Days", Next30Days,   
    {?Date Range} = "Other", {?Report Date Range}
 )

 

[bdreed35]

That is interesting as I can get it to work fine on the sample report I through together to mimick what you are doing.

What version of CR are you using?
What database are you using and which driver are using to access it from Crystal?
How did you define your Report Date Range Parameter?

~Brian

 

[lbass]

I think this error would arise if "Allow multiple values" is checked for {?Report Date Range}. Maybe you were wondering that, too, with your last question, Brian...

-LB

 

[dh42891]

Okay, I unchecked the "Allow Multiple Values" box for both parameters and that fixed the error message I was getting with the selection code.

I'm not entirely sure how

{?Date Range} = "Other", {?Report Date Range}

is supposed to work. Should that bring up a second window prompting for a date or is the user expected to enter both 'Other' for the first paramter and whatever date he wants for the second parameter, all in the same window?

Thanks for the replies, they have been very helpful,

dylan

 

[dh42891]

p.s. I'm using Crystal Reports 8.5, Pervasive SQL, ODBC.

 

[lbass]

It will bring up one window with two parameters shown. If the user responds to {?Date Range} by not choosing "Other", results will be returned without the user having to select the second parameter. But if the user chooses "Other", he/she would need to select and respond to the second parameter on the list in order for results to be returned. Why not play with it a bit to see how it works?

-LB