Parameter - date range - 2 different date fields.

[giggles7840]

Ok, im sure this is easy but im just not seeing it.

I have 2 date fields. date1 and date2. I need to create a param where date1 is the start of the range and date2 is the end of the range.

all records pulled would be user entered date1 and everything in between including user entered date2.

makes sense?

Im used to do date ranges with just one date field. its the 2 date fields thing that throwing me off.

Please help.

 

[checkai]

You can do different things, this is one:

In the record selection type the formula,

yourdatefield >= ?date1 and yourdatefield <= date2

also BETWEEN statements in IN statements

 

[synapsevampire]

Create a parameter of type date or datetime and select Range Values.

Now add something akin to the folloiwng to the record selection formula (Report->Edit Selection Formula->Record):

{Table.Order Date} = {?ParmDate}

In order to pass the SQL on to the database (check Database->Shoq SQL Query, it should reflect the dates), thereby increasing performance, you MAY have to convert the parameter values within formulas, and use those formulas in the record selection formula, as in:

@startdate
cdate(year(minimum({?StartDate})),month(minimum({?StartDate})),day(minimum({?StartDate})))

@enddate
cdate(year(maximum({?StartDate})),month(maximum({?StartDate})),day(maximum({?StartDate})))


{Table.Order Date} >= {@startdate}
and
{Table.Order Date} <= {@enddate}

-k

 

[dgillz]

If you have one parameter field, and it is a date RANGE parameter, just write a formula:

{YourDateField} in {?DateRangeParameter} Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports

http://www.trianglepartners.com

dgilsdorf@trianglepartners.com

 

[synapsevampire]

You don't need to use IN as dgillz suggests, the above solution using = does the same thing.

-k

 

[lbass]

Are you trying to limit records to those that start and end within the range? Then try:

{date1} in {?start} to {?end} and
{date2} in {?start} to {?end}

If there could be multiple dates per case, then you might need to add: and {date2} >= {date1}

If you are trying to limit records to those that start within the range or those that end within the range, then try:

{date1} in {?start} to {?end} or
{date2} in {?start} to {?end}

-LB