Calculating Date Difference to Use as Record Selection 1

[khan82]

Is it possible to place a select statements to only show records in a sub-report that has the following criteria:

I want the sub-report to only display leases that are over a year.

- First to calculate the difference between two records i.e. Lease start date and lease end date.

- Show record if the difference > 365 days.

*This sub-report is present in the group footer of the main report. Sub-report currently runs on a parameter passed through the main report. The main report show leases expiring in a certain range while the sub-report shows leases that are expiring on or before the main report range.


*The fields i would be working with are Actual_Start and Original_Lease_End

*The sup-report is grouped by LEASE.Property and below is the Select record formula.

{PML_LEASE__LEASE.Property} = {?Pm-PMP_PROPERTY__PROPERTY.Property} and
{PML_LEASE__LEASE.Original_Lease_End} < {?Date Range} and
{PML_LEASE__LEASE.Status} = "Current" and
{PML_LEASE__LEASE_RECURRING_CHG_LOG.Charge_Type} = "10" and
not ({PML_LEASE__LEASE_RECURRING_CHG_LOG.Status} in ["Future", "Historical"])

Thanks
AK

 

[MCuthill]

khan82,

I believe the DateDiff() Function should achieve the desired result. The syntax for this function is:
DateDiff(IntervalType,StartDate,EndDate)

For your scenario, I believe what you seek is to add the following to the subreport selection criteria:

[blue]DateDiff[/blue]("d",{Table.Actual_Start},{Table.Original_Lease_End}) > 365

Hope this helps, cheers!

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[khan82]

i think it works...i am sorting out through the results...will let you know. Thanks!