Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2020 : Report Design
Message Icon Topic: Selection Formula greater than date not working Post Reply Post New Topic
Author Message
SimonM
Newbie
Newbie


Joined: 07 May 2015
Online Status: Offline
Posts: 1
Quote SimonM Replybullet Topic: Selection Formula greater than date not working
    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
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet 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})
IP IP Logged
Post Reply Post New Topic
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.016 seconds.