Filtering inside Filtered Data

[Former Member]

Here is a real basic questions.  How would I write the formula to: exclude accounts with a Date Opened less than <= 2015,12,31 and Date Redeemed >= 2016,01,01.

I have include a screen shot of how the report looks at this point.  The first two data points I want to eliminate, but keep the remaining.

My initial Select Expert - Report filter is also attached.

Thanks - Adam

[Former Member]

Thanks Abhilash, looks like it is working.

[abhilash_kumar]

Hi Adam,

Add the following code after the last line in your selection formula:

AND

{Date Opened} >= #Dec 31, 2015# AND {Date Redeemed} <= #Jan 01, 2016#

-Abhilash

[Former Member]

Abhilash, the report came back blank.  It's like I need a "but if" the redeemed date is >= to 1/1/16 and the opened is <= 12/31/15, exclude the item from the list.

Adam

[abhilash_kumar]

Hi Adam,

Try:

AND

(

{Date Opened} >= #Dec 31, 2015# AND

(IsNull({Date Redeemed}) OR  {Date Redeemed} = cDate(0,0,0) OR {Date Redeemed} <= #Jan 01, 2016#)

)

-Abhilash

[Former Member]

Abhilash, I receive the following Boolean error.  Does everything else look correct?  I was also prompted to add the additional "))" at the end of the statement.

[abhilash_kumar]

You're missing a parenthesis after the field in the isNull function:

isNull({certificate_information.Date_Redeemed})

You'd also need to take out the extra parenthesis to the end.

-Abhilash

[Former Member]

Looks good, I will check out the details and let you know.

[Former Member]

Abhilash, very close.  Data points with a Renewal Date and Date Opened are being exclude and should be included.  See below.

[abhilash_kumar]

Could you list down all conditions of record selection please?

-Abhilash