Selection Formula greater than date not working
Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Report Design
Forum Discription: The best way to design a report and problems you have encountered
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=21867
Printed Date: 06 May 2024 at 5:42am
Topic: Selection Formula greater than date not working
Posted By: SimonM
Subject: Selection Formula greater than date not working
Date Posted: 03 Feb 2016 at 3:57pm
Hi,
We have Crystal Reports XI. I am running a report that is for an Access Database.
The report has 4 sub reports. For each subreport the following values are passed through.
?Sort Order
?Directorate
?Author
@DateRange
The four values are assigned from Parameter fields.
The "Report Date" parameter is set up as a Parameter Type "Date"
"@DateRange" is populated with the date that is selected from the "Report Date" parameter via the following formula:
{?Report Date}
I am encountering the following problem with my sub-report.
In the Record Selection Formula of the sub report I have the following:
(Date({tblActionSheetNote.acsDate})<{?Pm-@DateRange}-30)
tblActionSheetNote.acsDate is a Date Time field so I am converting it to just Date.
The idea of this filter is that, if the acsDate is more than 30 days older than the report date, the record is to be returned.
The problem is, when I run the query with a report date of 1 March 2016, it returns 1115 records with date ranges from 20/10/2010 to 19/01/2016. This sounds correct....
until I change the formula from Less Than to Greater Than (eg (Date({tblActionSheetNote.acsDate})>{?Pm-@DateRange}-30)
Now it returns only 129 records, BUT the date range for those records are 20/05/2010 to 19/01/2016???
If I run a query asking if it is less than a date, then I run the same query asking if it is greater than the date, why would it be returning records with the same date ranges?
Is there another (better) way I can check to see if one date is before or after another date?
Thanks
Simon
|
Replies:
Posted By: DBlank
Date Posted: 04 Feb 2016 at 2:33am
1. the date range of the results is displaying the date value from tblActionSheetNote.acsDate, correct?
2. place the ?Pm-@DateRange parameter field into the sub-report header to verify the value that was passed to it
3. try using the DATEADD() on it rather than -30
Date({tblActionSheetNote.acsDate})>DATEADD('d',-30,{?Pm-@DateRange})
|
|