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.

 

[lbass]

I don't believe this can be done.

-LB

 

[susanna123]

Let me give you a better example. maybe I didn't explain it well enough.

The above code for date range is a parameter that displays:
date range for: last 7 days, last 30 days, last 60 days etc

when any one of those values are chosen, data is returned for those specific date ranges.

I would like to change this one parameter to two paramenters.
start date range
end date range

allowing the user to choose start date and end date and then return data for those ranges.

If a calendar picker was not used is this still possible? If so how? thanks

 

[lbass]

I thought you were asking about changing the format of the datetime display in the parameter prompt screen. Of course you can use two parameters, as in in:

{table.date} >= {?StartDate} and
{table.date} <= {?EndDate}

-LB

 

[susanna123]

Hi LB,

I commented out the code for both start and end time:

Local StringVar dt := {AP_FinishedInstances.SI_STARTTIME} ;
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 what I have in the formula field for start time:

{AP_FinishedInstances.SI_STARTTIME} >= {?@Start Date} and
{AP_FinishedInstances.SI_STARTTIME} <= {?@End Date}

for end time parameter formula field is:

{AP_FinishedInstances.SI_ENDTIME} >= {?@Start Date} and
{AP_FinishedInstances.SI_ENDTIME} <= {?@End Date}

I am getting an error saying that it needs to be date time.

can you tell me what is wrong? thanks

 

[susanna123]

I also keep on getting a "this string is not numeric" error on this code. can someone explain to me why? thanks.

Local StringVar 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)

 

[lbass]

You need to use your converted field (the formula) in the selection statement. You should be creating parameters in the field explorer--not sure why you are showing a formula in the parameter name.

The non-numeric error means you are either hitting a null value or your string may not always be the same length.

Try the following:

Local StringVar dt;
if isnull({AP_FinishedInstances.SI_ENDTIME}) or
trim(len({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)

Then change your selection formula to:


{@startdate} >= {?StartDate and
(
{@enddate} = date(9999,9,9,9,9,9) or
{@enddate} <= {?EndDate}
)

This would allow in records where there is no end date and those where they are less than or equal to the selected end date.

-LB

 

[susanna123]

Hi LB
thank you for your quick response:

I'm still getting an error saying that a string is required. I am wondering what the dt := 99990909090909 means.

Local StringVar dt;
if isnull({AP_FinishedInstances.SI_ENDTIME}) or
trim(len({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)

thanks

 

[susanna123]

the actual part of the code it highlights is:

(len({AP_FinishedInstances.SI_STARTTIME}))

error message: a string is required here.

Much appreciated.

Thks.

 

[lbass]

Sorry, that should have been:

if isnull({AP_FinishedInstances.SI_ENDTIME}) or
trim(len({AP_FinishedInstances.SI_ENDTIME})) = "" then
dt := "99990909090909" else
dt := {AP_FinishedInstances.SI_ENDTIME}; //etc.

-LB

 

[susanna123]

Hi LB, no problem. it's not your fault actually. It was my typo. I am still getting an error following the code you provided: error message: a string is required here and it highlights: (len({AP_FinishedInstances.SI_ENDTIME}))

Local StringVar dt;
if isnull({AP_FinishedInstances.SI_ENDTIME}) or
trim(len({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)

Sorry to bother again..

 

[lbass]

That can't be the source of the error. Please make the change I suggested:

Local StringVar dt;
if isnull({AP_FinishedInstances.SI_ENDTIME}) or
trim(len({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)

-LB

 

[susanna123]

hi LB,

maybe i am missing something. I made the change and still getting the same error. Is it because i have to put it in two places. I have two formula fields one for si_endtime and one for si_starttime. I would like to know what you suggest. thanks.

 

[lbass]

Please verify that the datatype of {AP_FinishedInstances.SI_ENDTIME} is in fact a string.

-LB

 

[susanna123]

Hi LB,

the error is described below:

Error: The string is non-numeric
-Instance end time
{AP_FinishedInstances.si_endtime}: ""
var dt: ""
var yr: 0
var mo: 0
var dy: 0
vary hr: 0
var mn: 0
var sc: 0
exp dt [1 to 4]: ""

It indicates the string is non -numeric.
Si_endtime and si_startime datatype are both strings.

In the field explorer i see them as: SI_endtime: String[14] and SI_starttime: string [14]

What could the error possibly be? thanks.

 

[lbass]

Sorry, remove the len(). That should have been:

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)

-LB

 

[susanna123]

now I have it working! thanks LB. You are good!

In the earlier email, I wanted to have 2 parameters start and end date.

I now put this code as you suggested in the record selection:

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

but i now receive an error on this line: stating too many arguments have been given to this function

{?@End Date} = date(9999,9,9,9,9,9)

Thanks.

 

[lbass]

Replace date(9999,9,9,9,9,9) with datetime(9999,9,9,9,9,9)

-LB

 

[susanna123]

Hi LB,

That gave me no error but I think the field names aren't correct. This is what i changed it to:

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

I am now getting a date/time is required here error and it highlights {AP_FinishedInstances.SI_STARTTIME}

Seems to be a conflict with the datatype? Do you think I am on the right path?

 

[lbass]

NO, you must use your conversion formulas to change your fields to datetimes or dates, and then set up your parameters as dates.

I don't know what you are doing. You should be setting up your parameters in field explorer->parameter->new--without an "@" sign.

-LB