Getting Unwanted Records In A Formula

[Former Member]

Can someone tell why I'm getting records with no dates and data for the year 2007 when I enter a date range of 1/1/2008 to 5/31/2008 for the prompt (i.e. {?Closed Date}) ? The code used is below. Thanks ! ! !

{tracking_file_primary.F422#Closing_Date} in {?Closed Date} And IsNull({tracking_file_primary.FU035#Is_Portfolio_Loan}) Or

{tracking_file_primary.FU035#Is_Portfolio_Loan} = "No" Or

{tracking_file_primary.FU035#Is_Portfolio_Loan} = "N";

[Former Member · Accepted solution]

Can you try copying and pasting the logic I posted?

[Former Member · Accepted solution]

Brain/Pandabear,

I did use the code that Brian supplied (as shown below).

{tracking_file_primary.F422#Closing_Date} in {?Closed Date} And

(

IsNull({tracking_file_primary.FU035#Is_Portfolio_Loan}) Or

{tracking_file_primary.FU035#Is_Portfolio_Loan} = "No" Or

{tracking_file_primary.FU035#Is_Portfolio_Loan} = "N"

)

[Former Member · Accepted solution]

The below code (Brian's code) only gives all records with "No" in the "{tracking_file_primary.FU035#Is_Portfolio_Loan}" column.

{tracking_file_primary.F422#Closing_Date} in {?Closed Date} And

(

IsNull({tracking_file_primary.FU035#Is_Portfolio_Loan}) Or

{tracking_file_primary.FU035#Is_Portfolio_Loan} = "No" Or

{tracking_file_primary.FU035#Is_Portfolio_Loan} = "N"

)

[Former Member · Accepted solution]

Lenell,

Change the filter to Caps,

Uppercase the fields coming in

and check for " ".

{tracking_file_primary.F422#Closing_Date} in {?Closed Date} And

(

IsNull({tracking_file_primary.FU035#Is_Portfolio_Loan}) Or {tracking_file_primary.FU035#Is_Portfolio_Loan} = "" Or

UpperCase ({tracking_file_primary.FU035#Is_Portfolio_Loan}) = "NO" Or

UpperCase({tracking_file_primary.FU035#Is_Portfolio_Loan}) = "N"

)

[Former Member · Accepted solution]

That work ! ! ! It was the non-Null (Blank columns). I added the below statement and it worked. I though that Null statement was taking care of these type of records. Thanks ! ! !

{tracking_file_primary.FU035#Is_Portfolio_Loan} = "" Or

[Former Member · Accepted solution]

I'm glad that worked for you. Don't forget, it wouldn't have even

begun to work without Brian's parens being correct.

The Panda

[Former Member]

It looks like you need some parenthesis:

{tracking_file_primary.F422#Closing_Date} in {?Closed Date} And

(

IsNull({tracking_file_primary.FU035#Is_Portfolio_Loan}) Or

{tracking_file_primary.FU035#Is_Portfolio_Loan} = "No" Or

{tracking_file_primary.FU035#Is_Portfolio_Loan} = "N"

)

[Former Member]

Brian,

This decreased the number of records received.

[Former Member]

Brian,

I tried your solution, but it decreased the number of records received. Statements with parenthesis are done first . . . right ? If so, I tried the below formula and it still does not work (i.e. blank column and 2007 records when the dates supplied is 1/1/2008 through 5/31/2008).

({tracking_file_primary.F422#Closing_Date} in {?Closed Date}) And IsNull({tracking_file_primary.FU035#Is_Portfolio_Loan}) Or

{tracking_file_primary.FU035#Is_Portfolio_Loan} = "No" Or

{tracking_file_primary.FU035#Is_Portfolio_Loan} = "N";

[Former Member]

When I run just the first line, I receive the correct number of records (as shown in bold below).

({tracking_file_primary.F422#Closing_Date} in {?Closed Date})

but, when I add the rest of the code (as shown in bold below), I get records with nothing in {tracking_file_primary.F422#Closing_Date} and 2007 records

And IsNull({tracking_file_primary.FU035#Is_Portfolio_Loan}) Or

{tracking_file_primary.FU035#Is_Portfolio_Loan} = "No" Or

{tracking_file_primary.FU035#Is_Portfolio_Loan} = "N";

Anyone have any idea what's going on ?

[Former Member]

Hi Lenell,

Brians parens are enclosing everything after the "AND", whereas, apparently, yours are not.