Parameter prompt for date ranges 1

[susanna123]

I found this code for date ranges to choose from one one of the forums (see bottom) but I would like to have it so I can choose the dates using a calendar picker.
How can i modify this code so to have the user choose from a date time picker calendar to display the dates and time in this format for the calendar:

1/20/2010 1:50:02AM

The format for the calendar is displaying: YYYY-MM-DD hh:mm:ss

------------------------------------------


//WhilePrintingRecords ;
Global DateVar Range DateRange ;
DateRange:= (if {?Date Range} = "Last 7 Days" then CurrentDate-7 to CurrentDate
else if {?Date Range} = "Last 30 Days" then CurrentDate-30 to CurrentDate
else if {?Date Range} = "Last 60 Days" then CurrentDate-60 to CurrentDate
else if {?Date Range} = "Last 90 Days" then CurrentDate-90 to CurrentDate
else if {?Date Range} = "Last 120 Days" then CurrentDate-120 to CurrentDate
else if {?Date Range} = "Last Year (ie 365 Days)" then CurrentDate-365 to CurrentDate
else CurrentDate-365 to CurrentDate);
Minimum(DateRange) & " to " & Maximum(DateRange)

Thanks.

 

[susanna123]

Hi LB,

I understand what you mean but I'm a little confused. I changed the parameters to not show the "@" signs
I dont have a conversion formula field. here are the formula fields I have:

Elapsed time - Number
Elapsed Time - String at Group (this calculates the total elapsed time)
Elapsed - string at detail
Instance end time
Instance start time

Instance end time was what you helped me earlier:

Local StringVar dt;
if isnull({AP_FinishedInstances.SI_ENDTIME}) or
trim({AP_FinishedInstances.SI_ENDTIME}) = "" then
dt := "99990909090909" else
dt := {AP_FinishedInstances.SI_ENDTIME};
Local NumberVar yr := ToNumber(dt[1 to 4]);
Local NumberVar mo := ToNumber(dt[5 to 6]);
Local NumberVar dy := ToNumber(dt[7 to 8]);
Local NumberVar hr := ToNumber(dt[9 to 10]);
Local NumberVar mn := ToNumber(dt[11 to 12]);
Local NumberVar sc := ToNumber(dt[13 to 14]);
DateTimeValue (yr, mo, dy, hr, mn, sc)

Can the conversion formula be placed in this formula field as well? Can you provide me an example for the conversion for the end time field so I can try it out for the start time.

thank you very much.

 

[susanna123]

I also thought the above code that you resolve for me did the conversion. Maybe I am missing something. Kindly suggest. TY

 

[lbass]

THIS is your conversion formula:

//{@Instance End Time}:
Local StringVar dt;
if isnull({AP_FinishedInstances.SI_ENDTIME}) or
trim({AP_FinishedInstances.SI_ENDTIME}) = "" thendt := "99990909090909" else
dt := {AP_FinishedInstances.SI_ENDTIME};
Local NumberVar yr := ToNumber(dt[1 to 4]);
Local NumberVar mo := ToNumber(dt[5 to 6]);
Local NumberVar dy := ToNumber(dt[7 to 8]);
Local NumberVar hr := ToNumber(dt[9 to 10]);
Local NumberVar mn := ToNumber(dt[11 to 12]);
Local NumberVar sc := ToNumber(dt[13 to 14]);
DateTimeValue (yr, mo, dy, hr, mn, sc)

{@InstanceStartTime} >= {?Start Date} and
(
{@InstanceEndTime} = datetime(9999,9,9,9,9,9) or
{AP_FinishedInstances.SI_ENDTIME} <= {?End Date}
)

-LB

 

[susanna123]

Hi LB,

I'm sorry for the trouble. I just test that out. Getting confused and I like to learn how this works too.

I am getting confused as to where: {@InstanceStartTime} and {@InstanceEndTime} is referencing to in your code:

{@InstanceStartTime} >= {?Start Date} and
(
{@InstanceEndTime} = datetime(9999,9,9,9,9,9) or
{AP_FinishedInstances.SI_ENDTIME} <= {?End Date}
)

The error I get is: This field name is not known. Do you mean I should create a formula field called: {@InstanceEndTime} and {@InstanceStartTime} and place the above code:

//{@Instance End Time}:
Local StringVar dt;
if isnull({AP_FinishedInstances.SI_ENDTIME}) or
trim({AP_FinishedInstances.SI_ENDTIME}) = "" thendt := "99990909090909" else
dt := {AP_FinishedInstances.SI_ENDTIME};
Local NumberVar yr := ToNumber(dt[1 to 4]);
Local NumberVar mo := ToNumber(dt[5 to 6]);
Local NumberVar dy := ToNumber(dt[7 to 8]);
Local NumberVar hr := ToNumber(dt[9 to 10]);
Local NumberVar mn := ToNumber(dt[11 to 12]);
Local NumberVar sc := ToNumber(dt[13 to 14]);
DateTimeValue (yr, mo, dy, hr, mn, sc)

{@InstanceStartTime} >= {?Start Date} and
(
{@InstanceEndTime} = datetime(9999,9,9,9,9,9) or
{AP_FinishedInstances.SI_ENDTIME} <= {?End Date}
)

Thanks

 

[lbass]

It doesn't matter what the formula is called. Just reference it in the selection formula.

THIS is your conversion formula in your field explorer:

//{@Instance End Time}:
Local StringVar dt;
if isnull({AP_FinishedInstances.SI_ENDTIME}) or
trim({AP_FinishedInstances.SI_ENDTIME}) = "" thendt := "99990909090909" else
dt := {AP_FinishedInstances.SI_ENDTIME};
Local NumberVar yr := ToNumber(dt[1 to 4]);
Local NumberVar mo := ToNumber(dt[5 to 6]);
Local NumberVar dy := ToNumber(dt[7 to 8]);
Local NumberVar hr := ToNumber(dt[9 to 10]);
Local NumberVar mn := ToNumber(dt[11 to 12]);
Local NumberVar sc := ToNumber(dt[13 to 14]);
DateTimeValue (yr, mo, dy, hr, mn, sc)

This is your record selection formula, I guess with spaces in the formula name!:

{@Instance Start Time} >= {?Start Date} and
(
{@Instance End Time} = datetime(9999,9,9,9,9,9) or
{AP_FinishedInstances.SI_ENDTIME} <= {?End Date}
)

-LB

 

[susanna123]

Hi LB.
thank you for your prompt responses.

I tried the above.

Placed this in my record selection formula:

{@Instance Start Time} >= {?@Start Date} and
(
{@Instance End Time} = datetime(9999,9,9,9,9,9) or
{AP_FinishedInstances.SI_ENDTIME} <= {?@End Date}
)


Error is: {AP_FinishedInstances.SI_ENDTIME} - date time is required here.

 

[susanna123]

sorry my typoin the previous trail.

{@Instance Start Time} >= {?Start Date} and
(
{@Instance End Time} = datetime(9999,9,9,9,9,9) or
{AP_FinishedInstances.SI_ENDTIME} <= {?End Date}
)

My parameters were set to date time so I think that was why it was giving that date time is required here error.
setting the start and end date parameters to date time will also allow to enter the values with time but when I set the parameters to date it will only show date.

The error I get is:

{?End Date} - a string is required here

thanks.

 

[lbass]

{@Instance Start Time} >= {?Start Date} and
(
{@Instance End Time} = datetime(9999,9,9,9,9,9) or
{@Instance End Time} <= {?End Date}
)

I made another silly mistake--but you should be able to catch these.

-LB

 

[susanna123]

Hi LB.
You are brilliant. I'm still very new to crystal reporting explaining why I cannot troubleshoot well.

I wanted to ask what the difference was when the parameter start and end date was changed to type: date.

Does it matter if the types were: date/time

with the date/time type the times could also be entered into the parameter fields.

Thanks.

 

[lbass]

Set them up either way--usually users don't want to bother entering specific times, but if that fits your needs, then make them datetimes. Your formulas would have to return datetimes, which they do, so there should be no problem.

-LB

 

[susanna123]

Thanks LB!

As you suggested, I set that up.
I want to ask when I refresh the report, when it prompts for parameters, and fills in all fields. report runs. How cam i make it that the user does not have to fill in all parameters. I looked under the properties of each parameter field but unsure of which value to set to true/false. Basically i want the user to either enter values for the parameters or leave them blank and still the report runs.

 

[susanna123]

Is there a way to make the parameters optional as in "blanks" allowed?

thanks

 

[lbass]

Please start a new thread.

-LB