Problem with Record Selection Formula in Subreport - AND / OR 1

[JMSBR]

Hi

Please could you help this has been driving me crazy.

I am trying to filter a subreport using the Record Selection Formula. I have created 4 formula fields which Return "Print" or "Don't Print" depending on the formula.

What I want to do is set up the forumla so it only prints a record if any of the Formula fields = "Print"

So far I have tried:


{address_Cross_Referral.rd_A1_ReferredBy1} = {?Pm-fee_earner.fee_earner_name} and {@Instruction} = "Print"
or
{address_Cross_Referral.rd_A1_ReferredBy1} = {?Pm-fee_earner.fee_earner_name} and {@No Instruction} = "Print"
or
{address_Cross_Referral.rd_A1_ReferredBy1} = {?Pm-fee_earner.fee_earner_name} and {@Null} = "Print"
or
{address_Cross_Referral.rd_A1_ReferredBy1} = {?Pm-fee_earner.fee_earner_name} and {@Pending} = "Print"


I also tried...


{address_Cross_Referral.rd_A1_ReferredBy1} = {?Pm-fee_earner.fee_earner_name}
and {@Instruction} = "Print"
or
{@No Instruction} = "Print"
or
{@Null} = "Print"
or
{@Pending} = "Print"


I have also tried a number of combinations of brackets but whenever I run the report it only returns if the first OR is equal to "Print". For example if {@No Instruction} = "Print" then it won't return any results.

Please help as I just don't seem to be able to crack it.

Thanks

 

[hilfy]

SQL evaluates ands and ors sequentially. So if you have one or more multi-part conditions, you need parentheses around each of your OR clauses to get this to work.

This should work the way you want it to:

({address_Cross_Referral.rd_A1_ReferredBy1} = {?Pm-fee_earner.fee_earner_name} and {@Instruction} = "Print")
or
({address_Cross_Referral.rd_A1_ReferredBy1} = {?Pm-fee_earner.fee_earner_name} and {@No Instruction} = "Print")
or
({address_Cross_Referral.rd_A1_ReferredBy1} = {?Pm-fee_earner.fee_earner_name} and {@Null} = "Print")
or
({address_Cross_Referral.rd_A1_ReferredBy1} = {?Pm-fee_earner.fee_earner_name} and {@Pending} = "Print")

-Dell


A computer only does what you actually told it to do - not what you thought you told it to do.

 

[lbass]

I think you could simplify this by using:

{address_Cross_Referral.rd_A1_ReferredBy1} = {?Pm-fee_earner.fee_earner_name} and
"Print" in [{@Instruction},{@No Instruction},{@Null},{@Pending}]

Ordinarily you should show the content of formulas, by the way.

-LB

 

[JMSBR]

Thanks for your speedy reply

Unfortunatley neither idea worked, which I just don't understand. I added the formula fields to try and simplify the selection formula. I started off with:

{address_Cross_Referral.rd_A1_ReferredBy1} = {?Pm-fee_earner.fee_earner_name}
and
(
(
({address_Cross_Referral.rd_A1_ReferralStatus1} = "Pending" or isnull ({address_Cross_Referral.rd_A1_ReferralStatus1})or {address_Cross_Referral.rd_A1_ReferralStatus1} = "" or {address_Cross_Referral.rd_A1_ReferralStatus1} = " ")
)
or
(
({address_Cross_Referral.rd_A1_ReferralStatus1} = "Instruction" or {address_Cross_Referral.rd_A1_ReferralStatus1} = "No Instruction")
and {address_Cross_Referral.rd_A1_ReferredDate1} > dateadd ("m",-3,currentdate)
)
or
(
({address_Cross_Referral.rd_A1_ReferralStatus1} = "Instruction" or {address_Cross_Referral.rd_A1_ReferralStatus1} = "No Instruction")
and isnull ({address_Cross_Referral.rd_A1_ReferredDate1}
)
))

Does that give you any ideas?

 

[lbass]

If the other option for those formulas is "Not Print", then mine wouldn't have worked.

Your last post is totally confusing. Please explain in words under what conditions you want a row to print.

-LB

 

[JMSBR]

What I am trying to acheive is to return rows when {address_Cross_Referral.rd_A1_ReferredBy1} = {?Pm-fee_earner.fee_earner_name}

and then any of the following are true:

1) {address_Cross_Referral.rd_A1_ReferralStatus1} = "Pending"

2) isnull ({address_Cross_Referral.rd_A1_ReferralStatus1}

3) ({address_Cross_Referral.rd_A1_ReferralStatus1} = "Instruction" and isnull ({address_Cross_Referral.rd_A1_ReferredDate1}

4) ({address_Cross_Referral.rd_A1_ReferralStatus1} = "No Instruction" and isnull ({address_Cross_Referral.rd_A1_ReferredDate1}

5) ({address_Cross_Referral.rd_A1_ReferralStatus1} = "Instruction" and {address_Cross_Referral.rd_A1_ReferredDate1} is in the last 3 months

6) ({address_Cross_Referral.rd_A1_ReferralStatus1} = "No Instruction" and {address_Cross_Referral.rd_A1_ReferredDate1} is in the last 3 months

Hope that makes sense.

Thanks

 

[lbass]

Null checks for a specific field must precede any other reference to that field, so try:

{address_Cross_Referral.rd_A1_ReferredBy1} = {?Pm-fee_earner.fee_earner_name} and
(
isnull ({address_Cross_Referral.rd_A1_ReferralStatus1} or
{address_Cross_Referral.rd_A1_ReferralStatus1} = "Pending" or
(
(
isnull ({address_Cross_Referral.rd_A1_ReferredDate1}) or
{address_Cross_Referral.rd_A1_ReferredDate1} > dateadd ("m",-3,currentdate)
) and
{address_Cross_Referral.rd_A1_ReferralStatus1} in ["Instruction","No Instruction"]
)
)

-LB

 

[JMSBR]

Thanks, you don't know how much that has helped. It would have take me ages to get there.

Thanks again