Question about dates

[susanna123]

Original

DateRange formula:

//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)


Record selection:

Date({@Instance End Time}) in DateVar Range DateRange


It will display data based on the date range parameter selected.

For example if I run the report to get data from last year 365 days (Jan 2010 - 2011) this works,
how can I run it for Jan 2009 - Jan 2010?


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

Also, I tried to modify the above code to instead have one parameter to allow a user to choose a start and end date. However it is not working properly.


Instance Start Time Formula:

Local StringVar dt;
if isnull({AP_FinishedInstances.SI_STARTTIME}) or
trim({AP_FinishedInstances.SI_STARTTIME}) = "" then
dt := "99990909090909" else
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)

Instance End Time Formula:

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)


Record selection formula:


(if {?Start Date} <= date(01/01/1900) then
true
else

{@Instance Start Time} >= {?Start Date}) and
(

(if {?End Date} >= date(01/01/1900) then
true
else

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

I was not able to display the data within the range selected:
How can I get it to work in such a way that if we enter a date range of Jan 01 2010 to Jan 01 2011 we will get data whose last run time was between those 2 dates.

any help is much appreciated.

Thanks.

 

[jmd0252]

I am on Crystal XI. My data base has dates, that are defined as strings, so I create 2 Parameter Fields. One for beginning, and one for ending. and then go thru the select expert, and define a criteria, where my main tbale, only has those records for dates withing that range. Now because they are strings,, the users, have to enter them "funny", such as 20100101, for january 1, 2010. But is that a big deal?? Would something like that work for you. I know if you define the parametres as "date":,, then it wants say 2010-01-01,, as the format. Would that be a problem?

 

[susanna123]

Hi the formulas works and it's similar to what you did.

However the problem is that, it's returning too much data and not within the range. Would you be able to find out what's wrong with the formula?

Thanks.